欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

davinci中报表sql有关写法

程序员文章站 2022-05-28 09:17:58
...

 

davinci中报表sql有关写法

 

 

有case when 统计分组后的同类列值-----经过聚合函数统计之后不是数字类型的都变成数字类型的了----作为指标

 

sql中的变量用$$占位,和设置的变量要一致---只有设置变量后才可在页面设置控制控件,sql占位中的默认值选定后需要点击确认,否则没有选成功,会报null

 

 

 

    select

        date(a.`日期`) as `日期`,

    date_format(a.`日期`,'%Y') as 年份,

concat('Q',quarter(a.`日期` )) as 季度,

concat( date_format(a.`日期`,'%Y'),"_",concat('Q',quarter(a.`日期` ))) AS 年_季度,

    a.`不变价GDP分项` as 不变价GDP分项,

a.`不变价GDP同比`*0.01 as 不变价GDP同比

     from `INDEX_HG_GDP_RATE` a

 

 

select * from R_PRICE_BR_GJ_MP

 

 

 

    select

        date(a.`日期`) as `日期`,

    date_format(a.`日期`,'%Y') as 年份,

concat('Q',quarter(a.`日期` )) as 季度,

concat( date_format(a.`日期`,'%Y'),"_",concat('Q',quarter(a.`日期` ))) AS 年_季度,

    a.`不变价GDP分项` as 不变价GDP分项,

a.`不变价GDP同比`*0.01 as 不变价GDP同比

     from `INDEX_HG_GDP_RATE` a

 

 

 

 

SELECT

`year` AS `年份`,

case-------------------------------------

 when  `INDEXCODE`  = 'YS_0000106444' then '澳大利亚'

WHEN `INDEXCODE`  = 'YS_0000106445' THEN '巴西'  -------------------then就是这列的最终值

WHEN `INDEXCODE`  = 'YS_0000106448' THEN '俄罗斯'

WHEN `INDEXCODE`  = 'YS_0000106456' THEN '新喀里多尼亚'

WHEN `INDEXCODE`  = 'YS_0000106449' THEN '菲律宾'

WHEN `INDEXCODE`  = 'YS_0000106457' THEN '印尼'

WHEN `INDEXCODE`  = 'YS_0000106458' THEN '中国'

WHEN  `INDEXCODE`  = 'YS_0000106452' THEN '加拿大'

else null

end  as '国家',-------------------------------------------

 `DATAVALUE`  as '产量'

FROM `f9_nie_zhzs` 

where `year`in($year$)

 and `INDEXCODE` in ('YS_0000106444','YS_0000106445', 'YS_0000106448','YS_0000106456','YS_0000106449','YS_0000106457','YS_0000106458' , 'YS_0000106452')

 

 

 

 

SELECT

`DATADATE`  as '日期',

sum(-----------------------------------

(

CASE----------------

WHEN (

 `INDEXCODE`  ='YS_0000191631'

) THEN

 `DATAVALUE`       ------------要统计的字段,普通的sum是统计整列,用case 可以统计列中同类值

ELSE

NULL

END-----------------------

)

) AS 'YS_0000191631',-------------------------------

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0000191633'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0000191633',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='ST_0000299954'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'ST_0000299954',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0000068600'

 

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0000068600',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0000993195'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0000993195',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0000993196'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0000993196',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0000993197'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0000993197',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0002560384'

 

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0002560384',

 

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0003985719'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0003985719',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='FU_0000557459'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'FU_0000557459',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='Sa_0000177252'

 

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'Sa_0000177252',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='ST_0000076917'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'ST_0000076917',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0003985721'

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0003985721',

sum(

(

CASE

WHEN (

 `INDEXCODE`  ='YS_0003985720'

 

) THEN

 `DATAVALUE`

ELSE

NULL

END

)

) AS 'YS_0003985720'

FROM  `f9_nie_qxjc` 

group by  `DATADATE`

 

 

 

 

 

 

变量名称只要是sql中有的即可

 

 

select

date_format(日期,'%Y-%m-%d') as '日期',

地区,

sum(

(

CASE

WHEN (

钢材品种='螺纹'

) THEN

 建龙价格

ELSE

NULL

END

)

) AS '螺纹-建龙价格',

sum(

(

CASE

WHEN (

钢材品种='螺纹'

) THEN

 汇总价格

ELSE

NULL

END

)

) AS '螺纹-汇总价格',

sum(

(

CASE

WHEN (

钢材品种='螺纹'

) THEN

 期货主力价格

ELSE

NULL

END

)

) AS '螺纹-期货主力价格',

sum(

(

CASE

WHEN (

钢材品种='盘螺'

) THEN

 建龙价格

ELSE

NULL

END

)

) AS '盘螺-建龙价格',

sum(

(

CASE

WHEN (

钢材品种='盘螺'

) THEN

 汇总价格

ELSE

NULL

END

)

) AS '盘螺-汇总价格',

sum(

(

CASE

WHEN (

钢材品种='盘螺'

) THEN

 期货主力价格

ELSE

NULL

END

)

) AS '盘螺-期货主力价格',

 

sum(

(

CASE

WHEN (

钢材品种='热卷'

) THEN

 建龙价格

ELSE

NULL

END

)

) AS '热卷-建龙价格',

sum(

(

CASE

WHEN (

钢材品种='热卷'

) THEN

 汇总价格

ELSE

NULL

END

)

) AS '热卷-汇总价格',

sum(

(

CASE

WHEN (

钢材品种='热卷'

) THEN

 期货主力价格

ELSE

NULL

END

)

) AS '热卷-期货主力价格'

from gangcaifenquyujiagequshi

group by 日期,地区

 

======注册用户分正式和非正式用户分类占比饼图====

 

SELECT

multiIf(isNull("非正式用户"),0,"非正式用户")+3432-3432+605 AS "非正式用户" ,

multiIf(isNull("正式用户"),0,"正式用户")+113+549 AS "正式用户"

FROM

(SELECT 

COUNT(

  DISTINCT(

  CASE

    WHEN (auth_type NOT IN ('1') ) THEN

  uid

  ELSE

   NULL

  end

)) as "非正式用户",

COUNT(

  DISTINCT(

  CASE

    WHEN ( auth_type IN ('1')) THEN

  uid

  ELSE

   NULL

  end

)) as "正式用户"

FROM  

(

   SELECT uid,auth_type

  FROM daily_new_clientrpt_master mm left join daily_new_clientrpt_slave ss ON mm.event_id=ss.event_id  WHERE mm.event_code='101101000100016'  AND ss.event_property_code='101101000100016003' AND mm.event_id IN

(SELECT max(event_id) FROM daily_new_clientrpt_master m left join daily_new_clientrpt_slave s ON m.event_id=s.event_id WHERE m.event_code='101101000100016'  AND event_property_code='101101000100016003' GROUP BY uid) 

 

 

) T )

 

 

 

=====用户根据注册渠道分类====

 

SELECT "注册来源",

( case when( "注册来源"='客户端')then

                    "数量"+50+668+549

                      else

                     "数量"

                     end

                    ) AS "数量"

                    FROM

(                    

SELECT

 (case 

                        when( event_property_value='0' )then

                        '客户端'

                     when ( event_property_value='1') then

                         '手机版'

                     when ( event_property_value='2' ) then

                         '管理端添加'

          when ( event_property_value='3') then

                         '外部用户导入'

                     when ( event_property_value='4') then

                         'CRM新增'

                      when ( event_property_value='5' ) then

                         '主站网页版新增'

    when ( event_property_value='6' ) then

                         '会议邀请'

  when ( event_property_value='7' ) then

                         '活动'

  when ( event_property_value='8' ) then

                         '管理员邀请'

                        else

                           '其他'

                      end

                     ) as "注册来源" ,

                     "数量"

FROM

(

SELECT

event_property_value,event_property_code,COUNT( DISTINCT(uid)) AS "数量"

from

(

  SELECT event_property_value,event_property_code,uid

  FROM daily_new_clientrpt_master mm left join daily_new_clientrpt_slave ss ON mm.event_id=ss.event_id  WHERE mm.event_code='101101000100016'  AND ss.event_property_code='101101000100016003' AND mm.event_id IN

(SELECT max(event_id) FROM daily_new_clientrpt_master m left join daily_new_clientrpt_slave s ON m.event_id=s.event_id WHERE m.event_code='101101000100016'  AND event_property_code='101101000100016003' GROUP BY uid) 

 

 

) GROUP BY event_property_value,event_property_code)) 

 

 

==导入/导出===

 

 

 

SELECT

"指标代码",

"指标名", 

"所属库", 

"所属部分",

T1."提取量",

T2."导出量"

from

 

(

  SELECT 

 

 arrayElement(splitByChar('|',event_property_value),1) as "指标代码", 

 arrayElement(splitByChar('|',event_property_value),2) as "指标名", 

 arrayElement(splitByChar('|',event_property_value),3) as "所属库", 

 arrayElement(splitByChar('|',event_property_value),4) as "所属部分",

  COUNT(

   (CASE 

     WHEN (event_code='101101000800016') THEN

   "指标代码"

   else

   null

   end)

 ) AS "提取量"

 

FROM

(SELECT s.event_code, s.event_property_code,s.event_property_name,s.event_property_value 

 FROM  default.daily_new_clientrpt_slave s 

 WHERE s.event_property_code != '' AND s.event_property_code ='101101000800016005' AND  (  s.event_code = '101101000800016')  AND day = today() LIMIT 10) T 

WHERE  "所属库" !=''AND "指标名"!='undefined' GROUP BY "指标代码","指标名", "所属库", "所属部分"  

) T1 

 

left JOIN 

(SELECT 

 

 arrayElement(splitByChar('|',event_property_value),1) as "指标代码", 

 arrayElement(splitByChar('|',event_property_value),2) as "指标名", 

 arrayElement(splitByChar('|',event_property_value),3) as "所属库", 

 arrayElement(splitByChar('|',event_property_value),4) as "所属部分",

 

   COUNT(

   (CASE 

     WHEN (event_code='101101000800027') THEN

   "指标代码"

   else

   null

   end)

 ) AS "导出量"

 

FROM

(SELECT s.event_code, s.event_property_code,s.event_property_name,s.event_property_value 

 FROM  default.daily_new_clientrpt_slave s

 WHERE s.event_property_code != '' AND s.event_property_code ='101101000800027005' AND  (  s.event_code = '101101000800027') AND day = today()) T 

WHERE  "所属库" !='' AND "指标名"!='undefined' GROUP BY "指标代码","指标名", "所属库", "所属部分") T2

ON T1."指标代码"=T2."指标代码" AND T1."所属库"=T2."所属库" AND  T1."所属部分"=T2."所属部分"  ORDER BY "指标代码" DESC

 

 

相关标签: davinci