sql使用说明(创建我的数据)

书写规则

  1. 别名字段合法性：字母开头，只能包含数字、字母、下划线
  2. 查询字段不可使用 *
  3. 字段名称有特殊字符、数字 需要使用 ``

select 1,database from table

  1. 常量、方法产生的字段 需要设置别名

    select 1 as a
    select ‘a’ as a
    select count(1) as total from table

  2. 参数变量

可在sql中使用参数变量进行占位，真正在仪表盘内对变量进行赋值（仪表盘设置变量、下拉搜索绑定图表或表的参数变量）
使用方法 : ‘${变量 | 模拟值 | 是否必填}’
- 变量: 在仪表盘中 通过仪表盘设置变量、下拉搜索绑定图表或表的参数变量 进行赋值。在仪表盘中，需要对变量进行赋值，若不赋值或赋值为空，则相当于替换此处条件为true。eg: select app_name,app_desc from app where app_id = ‘${app_id | 123}’ and app_desc = ‘1’；在仪表盘中，未对app_id 进行赋值，执行结果为 select app_name,app_desc from app where app_desc = ‘1’;

  • 模拟值: 可理解为默认值, 在查看我的数据时, 参数会替换为模拟值。
    eg: 将此SQL保存为我的数据 select app_name,app_desc from app where app_id = ‘${app_id | 123}’；在<我的数据>列表查看时，展现的数据为 select app_name,app_desc from app where app_id = ‘123’;

    - 是否必填:此参数只可填写 must / optional. 此参数为选填，默认为optional。
    must 此参数为必须填写，例如 一般用于接收url传参，比如单活动分析，活动ID应为必填
    optional 非必须填写

注意事项 :
1. 必须用双引号
2. 使用格式只限于 ‘${变量 | 模拟值}’ 或 ‘${变量 | 模拟值 | 是否必填}’，值的填写顺序不能颠倒
3. 模拟值仅限于在<我的数据>展现使用，在仪表盘使用变量时需要另赋值，如不赋值查询SQL会去除该过滤条件

  1. select app_name,app_desc from app where  app_id = '${app_id | 123}';
  2. select sum(num) num,sum(num1) num1 from table where app_id = '${app_id | 0}' group by app_id;
  1. 语句变量 - 可定义sql代码块
    1. 正常：
    2. select (select count(1) from dataview_connection) / (select count(1) from dataview_connection_mysql)
    3. 可视化：
    4. ${sql1} = select count(1) from dataview_connection;
    5. ${sql2} = select count(1) from dataview_connection_mysql;
    6. select ${sql1} / ${sql2}  as t1;
  1. 多行语句使用

可输入多行语句，“;”分隔，最终按最后一条返回数据，之前语句都是用于定义变量

  1. 系统变量

集成token验证后，sql语句中可直接使用 token解析出来的变量.
系统变量使用时，必须是非空字段
SQL帮助 - 图1

SQL帮助 - 图2

SQL帮助 - 图3
在添加系统变量的时候，还可以指定扩展变量
SQL帮助 - 图4
如上图，在我的数据插入系统变量时，使用 ${SYS_EXTEND.field} 即可

  1. eval 函数

使用eval 可在执行最终语句前，预先计算当前语句的值，但仅支持单值

  1. ${v1} = eval(select sum(custom_num) as v1 from custom_last_stat where day = date_sub(curdate(),interval 2 day) and company_id='${companyId}' and department_id in (${departments}) );
  2. ${v2} = eval(select ifnull(sum(custom_num),0) as v2 from custom_last_stat where day = date_sub(curdate(),interval 1 day) and company_id='${companyId}' and department_id in (${departments}));
  3. select ((${v2} - ${v1}) / ${v1}) as t1;
  1. 分支语句的支持

支持ifnull,case…when…else,if(condition,exprValue,exprValue)

  1. SELECT IF(`XB` ='男','1','2')  a FROM student01
  1. SELECT IFNULL(`bz`,'空值') a FROM zhuanjia
  1. SELECT
  2. '网络咨询（问诊）服务' AS namee,
  3. tc.org_name AS yiyuan,
  4. tc.org_code AS yiyuanbm,
  5. (CASE WHEN tc.patient_age BETWEEN '0岁' AND '19岁' THEN '0岁-20岁' 
  6. WHEN tc.patient_age BETWEEN '21岁' AND '29岁' THEN '20岁-30岁'
  7. WHEN tc.patient_age BETWEEN '30岁' AND '39岁' THEN '30岁-40岁' 
  8. WHEN tc.patient_age BETWEEN '40岁' AND '49岁' THEN '40岁-50岁' 
  9. WHEN tc.patient_age BETWEEN '50岁' AND '59岁' THEN '50岁-60岁' 
  10. WHEN tc.patient_age BETWEEN '60岁' AND '69岁' THEN '60岁-70岁' 
  11. WHEN tc.patient_age BETWEEN '70岁' AND '79岁' THEN '70岁-80岁' 
  12. WHEN tc.patient_age BETWEEN '80岁' AND '89岁' THEN '80岁-90岁' 
  13. WHEN tc.patient_age >= '90岁' THEN '>90岁' END) AS paage,
  14. COUNT(1) AS syl,
  15. tc.upload_time  AS rq
  16. FROM t_consult tc
  17. GROUP BY rq,yiyuan,paage
  1. 不支持if, case...when 嵌套
  1. 可以使用#注释,多行也用#
  1. #此行是注释
  1. sql变量与事件变量可以一起使用
  1. {sql_1} = select project_name aaa from dataview_project where project_id='${event_id|01ba712c9d144f3086f8f875310a1fd4|must}';
  2. {sql_2} =select username aaa from dataview_user where user_id='${event_id|468e46d55bfd493fb904d47922015da7|must}';
  3. SELECT {sql_1} bbbbb
  4. UNION
  5. {sql_2} ;

between and与变量

  • 当变量没有值,并且非必填,则相当于>=或者<=
    1. ## >=
    2. select title,url,time 
    3. from zhaobiao 
    4. where time BETWEEN '2020-05-23' and '${event_time|0}'

SQL帮助 - 图5

  1. ##<=
  2. select title,url,time 
  3. from zhaobiao 
  4. where time BETWEEN '${event_time|0}' and '2020-05-24'

SQL帮助 - 图6

  • 原本sql中变量没有赋值,在仪表盘给变量赋值之后 相当于between ‘2020-05-23’ and ‘2020-05-25’

SQL帮助 - 图7

  • 原本sql中变量没有赋值,在仪表盘给两个变量都赋值之后 相当于between ‘2020-05-23’ and ‘2020-05-24’

SQL帮助 - 图8

  • 原本sql中变量没有赋值,在仪表盘给一个变量赋值之后 相当于>=’2020-05-24’
  1. select title,url,time 
  2. from zhaobiao 
  3. where time BETWEEN '${event_A|0}' and '${event_B|1}'

SQL帮助 - 图9

  • 单变量赋同样的值,相当于 ==’2020-05-24 22:10:21’
    1. select title,url,time 
    2. from zhaobiao 
    3. where time BETWEEN '${event_C|0}' and '${event_C|1}'

SQL帮助 - 图10

  • 不同变量均不赋值,相当于 where 1,显示全部数据
    1. select title,url,time 
    2. from zhaobiao 
    3. where time BETWEEN '${event_V|0}' and '${event_V1|1}'

SQL帮助 - 图11

可选变量like

写法同mysql中的like,后面跟模糊表达式,%可以放在任意位置
where a like ‘%${Id | 1234}’ 必须以变量结尾
where a like ‘${Id | 1234}%’ 必须以变量开始
where a like ‘%${Id | 1234}%’ 只要包含变量就可以
示例:
SQL帮助 - 图12

ETL和INTERVAL函数

对区间进行分组并且统计落在各区间内的数据量

  1. select elt(interval(a.pv,0, 10, 50, 100), '小于10次', '10-50次', '50-100次', '100次以上') as amt_level, count(a.user_id) as cnt
  2. from `mp_shopping_stat`.`user_total_stat` a   
  3. group by elt(interval(a.pv, 0, 10, 50, 100), '小于10次', '10-50次', '50-100次', '100次以上');