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
相关推荐
ChatGPT接入项目实例 Python 调用davinci 002 api 示例 text-davinci-002
从今天开始，我会持续的讲解Davinci从入门到放弃相关视频。视频和相关的软件，我都会以百度云盘的方式，长期免费分享！ 视频和相关软件以百度云盘的方式，分享出来如下： 链接：...
Vector 给出的简易教程，如何将AUTOSAR模块添加到Davinci Configurator中
The Da Vinci Code - Dan Brown.pdf The Da Vinci Code - Dan Brown.pdf
DaVinCi 配置 EcuM模块的技术手册，内容涉及EcuM功能模块介绍，API接口等.基于RH850芯片的DaVinCi诊断模块配置手册。虽然说是RH850芯片包里的，但是文档内的内容是标准化的，通用化的。包括函数接口等服务都是通用的
Vector官方帮助文档，配置使用手册。从新建DaVinci工程开始一步一步的讲解如何配置工程；如何编译生成C代码；如何导入CDD、DBC等文件。手册讲解细致，可以说是手把手教学了
AUTOSAR 就是AUTomotive Open System ARchitecture，MATLAB + DaVinci（国内主流）进行AUTOSAR开发车用ECU软件的工具链使用说明文档。
DaVinCi 配置 BswM模块的技术手册，内容涉及模块介绍，状态管理，以及各API接口
汽车编程-德国Vector DaVinci Configurator Pro AUTOSAR标准的软件配置工具软件.docx
Ｖｅｃｔｏｒ Ｄｅｖｅｌｏｐ使用指导手册
DaVinci Resolve 12.5 mac 是一款为Mac OS 平台开发的达芬奇调色系统,DaVinci Resolve mac 即是大家俗称的达芬奇12,DaVinci Resolve mac 破解 是与好莱坞电影公司合作开发的世界顶级的调色软件,被广泛的用于电影....
DAVINCI开发中的Codec实例,适合初学者学习DAVINCI开发的架构
DaVinCi 配置 PduR模块的技术手册，内容涉及PduR功能模块介绍，API接口等.基于RH850芯片的DaVinCi诊断模块配置手册。虽然说是RH850芯片SIP包里的，但是文档内的内容是标准化的，通用化的。包括函数接口等服务都是...
详细的介绍了达芬奇的相关知识，对初学者对DAVINCI的了解很有帮助
Davinci相关资料,包括视频前端和后端,arm和DSP子系统资料.
1.修改配置文件，密码不是qq密码，首先需要在qq邮箱中开启SMTP服务， ...项目地址：https://github.com/edp963/davinci-docker 访问地址：https://edp963.github.io/davinci/quickStart.html 作者：AinUser
SEED davinci DM6467 用户指南
davinci 的开发结构描述 codecengin的结构描述
Davinci 是一个 DVAAS（Data Visualization as a Service）平台解决方案，面向业务人员/数据工程师/数据分析师/数据科学家，致力于提供一站式数据可视化解决方案。既可作为公有云/私有云独立部署使用，也可作为可视...
德州仪器推出的davinci技术，采用了arm+dsp的架构，通过codec engine编解码引擎，实现arm和dsp的通信，利用双核优势，占据嵌入式开发很大一批市场。 这是收集的重要的davinci技术开发资料，经典。