davinci中报表sql有关写法
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
推荐阅读
-
有关mysql中sql的执行顺序的小问题
-
sql server中查找特定类别的列的写法
-
Oracle中PL/SQL中if语句的写法介绍
-
有关数据库SQL递归查询在不同数据库中的实现方法
-
有关mysql中sql的执行顺序的小问题
-
oracle数据库中慢SQL的优化过程（awr报表为例）
-
Oracle中PL/SQL中if语句的写法介绍
-
sql server中format函数的yyyyMMddHHmmssffff时间格式兼容旧版sql写法
-
SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法
-
有关数据库SQL递归查询在不同数据库中的实现方法