精简版
0
0
0
云栖社区 > 数据中台 > 博客 > 正文
伴弋 2019-12-06 13:50:23 浏览 459
SQL
配置
Image
数据分析
展开阅读全文
作者: 潘炎峰 更多内容详见数据中台官网 https://dp.alibaba.com/
Quick B 的 SQL 传参建模功能提供基于 SQL 的数据加工处理能力, 减轻了 IT 支撑人员的工作量. 在即席查询 SQL 中, 我们用 ${物理字段: 显示别名} 来表示参数的占位符, 配置完占位符后, 就可以在查询控件中进行参数绑定. 在 Quick BI 的 SQL 传参建模中, 本质透传的是逻辑条件, 执行过程中会将 "${物理字段: 显示别名}" 替换为 "物理字段 > 查询值" 或 "物理字段 = 查询值" 或 "物理字段 in (查询值 A, 查询值 B, 查询值 C)" 或 "物理字段 >= 查询值 A and 物理字段 <= 查询值 B". 下面我们来看看, 哪些场景会用到 Quick B 的 SQL 传参建模功能吧!
场景一: 任意时间段内的用户购买行为分析
场景描述: 在零售行业往往需要分析用户的活跃度和客户的忠诚度, 那么通过分析任意时间段内用户购买频率是常用的分析思路.
基于 SQL 传参如何实现:
1) SQL 建模, 这个场景就是典型的二次聚合分析, 而且任意时间段, 需要作用到子查询中, SQL 建模语句如下:
- select
- sum(case when buy_cnt = 1 then 1 else 0 end ) as buy_cut_1,
- sum(case when buy_cnt = 2 then 1 else 0 end ) as buy_cut_2,
- sum(case when buy_cnt>2 and buy_cnt<=5 then 1 else 0 end ) as buy_cut_2_5,
- sum(case when buy_cnt>5 and buy_cnt<=10 then 1 else 0 end ) as buy_cut_5_10,
- sum(case when buy_cnt>10 and buy_cnt<=20 then 1 else 0 end ) as buy_cut_10_20,
- sum(case when buy_cnt>20 then 1 else 0 end ) as buy_cut_up_20
- from
- (SELECT a.customer_name,
- count(a.order_id) as buy_cnt
- from company_sales_record_copy a
- WHERE ${
- a.report_date : date_test
- } / 定义查询时间参数 /
- group by a.customer_name
- ) b
2) 参数设置, 在 Quick BI 中 SQL 传参本质上传逻辑条件, SQL 传参需要选择为 "日期 - 年月日", 供查询控件中识别数据类型.
3) 点击 "创建数据集", 构建 "任意时间多次购买客户数" 数据集
4) 仪表板配置, 以交叉表配置为例. 选择对应 SQL 传参建模数据集, 拖拽选择需要展现的字段. 如下所示:
5) 查询项绑定配置, 选择 SQL 参数项作为查询条件项, 设置查询项与图表组件的关联关系和筛选项展现形式. 如下图所示:
6) 数据验证: 点击查询进行数据验证和 SQL 准确性验证. Quick BI 的 SQL 引擎根据查询条件配置和 SQL 传参建模进行参数绑定. 如下图所示:
通过以上六步操作, 就可以很好的实现 SQL 传参建模的全流程穿越, 以此来支撑多次聚合的复杂分析场景.
场景二, 销售库存类数据分析 (SQL 建模供参考)
1.场景描述: 在零售行业分析任意时间段的库存和销量数据是比较常用的需求, 通过该数据分析甄别某个商品近期销售情况和库存? 针对这样的场景, 如何构建 Quick BI 的 SQL 传参建模?
1) 数据样例, 后台数据具体字段包括 (统计日期, 省份, 城市, 期初库存, 进货量, 出货量, 期末库存等):
2) 需要实现结果说明, 基于以上数据, 假设需要分析 20190801~20190820 浙江杭州的销售库存数据, 需要给出的结果为:(期初库存取汇总开始时间的期初库存值, 期末库存取汇总结束时间的期末库存值, 进货朗, 出货量采用 sum 汇总), 另外时间为筛选区间.
3) Quick BI 中 SQL 建模语句如下 (供参考):
- SELECT QBI_T_1_.COL_2 AS '省份',
- QBI_T_1_.`COL_3` AS '城市',
- sum( case when QBI_T_1_.COL_1 = start_date then QBI_T_1_.COL_4else 0 end) as '期初库存',
- SUM(QBI_T_1_.COL_5) AS '进货量',
- SUM(QBI_T_1_.COL_6) AS '出货量',
- SUM(case when QBI_T_1_.COL_1 = end_date then QBI_T_1_.COL_7 else 0 end ) as '期末库存'
- FROM quickbi_test.QBI_0808_1566542575222 AS QBI_T_1_ / 每日库存销量表 /
- left join (
- select min(a.COL_1) as start_date,
- max(a.COL_1) as end_date
- FROM quickbi_test.QBI_0808_1566542575222 AS a / 每日库存销量表 /
- where ${
- a.COL_1:report_date
- } / 查询项中绑定的 SQL 传参 /
- ) b /* 获取待汇总的统计时间 */
- on (
- QBI_T_1_.COL_1>= start_date
- and QBI_T_1_.COL_1 <= end_date
- )
- group by QBI_T_1_.COL_2,
- QBI_T_1_.`COL_3`
阿里巴巴数据中台团队, 致力于输出阿里云数据智能的最佳实践, 助力每个企业建设自己的数据中台, 进而共同实现新时代下的智能商业!
阿里巴巴数据中台解决方案, 核心产品:
Dataphin, 以阿里巴巴大数据核心方法论 OneData 为内核驱动, 提供一站式数据构建与管理能力;
Quick BI, 集阿里巴巴数据分析经验沉淀, 提供一站式数据分析与展现能力;
Quick Audience, 集阿里巴巴消费者洞察及营销经验, 提供一站式人群圈选, 洞察及营销投放能力, 连接阿里巴巴商业, 实现用户增长.
欢迎志同道合者一起成长! 更多内容详见数据中台官网 https://dp.alibaba.com/
来源: https://yq.aliyun.com/articles/738158