SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)
FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b
ON a.account_id=b.account_id ANDb.stdate<=a.stdate
GROUP BY a.account_id,a.stdate
ORDER BY a.account_id,a.stdate
2)滑动累计
返回账户固定窗口时间内累计消费和平均值
SELECT a.account_id,a.stdate ,SUM(click_pay),AVG(click_pay)
FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b
ON a.account_id=b.account_id ANDb.stdate<=a.stdate
AND b.stdate>=DATE_ADD(a.stdate,INTERVAL -30 DAY)
GROUP BY a.account_id,a.stdate
ORDER BY a.account_id,a.stdate
3)MTD累计
返回账户月初以来累计消费和平均值
SELECT a.account_id,a.stdate,SUM(click_pay),AVG(click_pay)
FROM data_account_csm_daily a INNER JOIN data_account_csm_daily b
ON a.account_id=b.account_id ANDb.stdate<=a.stdate
AND b.stdate>=DATE_FORMAT(a.stdate,”%Y-%M-01”)
GROUP BY a.account_id,a.stdate
ORDER BY a.account_id,a.stdate 再探讨下ROLLUP和CUBE。假设用户需要对N个维度进行聚合操作,需要进行N次GROUP BY再将结果进行UNION,而使用ROLLUP可以一次查询出N次GROUP BY 操作的结果。下面的两条语句查询结果一致,执行计划上却不同,前者只需要扫描一次,后者则需要扫描表四次。
语句1:
SELECT col1,col2,col3,SUM(col4) FROM table
GROUP BYcol1,col2,col3
WITH ROLLUP 语句2:
SELECT col1,col2,col3,SUM(col4) FROM table
GROUP BYcol1,col2,col3
UNION
SELECT col1,col2,NULL,SUM(col4) FROM table
GROUP BYcol1,col2
UNION
SELECT col1,NULL,NULL ,SUM(col4) FROM table
GROUP BY col1
UNION
SELECT NULL,NULL,NULL,SUM(col4) FROM table 与ROLLUP只在同一层次上对维度进行汇总不同,CUBE对所有维度进行汇总,N个维度CUBE需要2的N次方分组操作。当前版本的MySQL还不支持CUBE操作,但和用多个GROUP操作UNION模拟ROLLUP同理,也可以用多个ROLLUP操作UNION模拟CUBE。
子查询vs JOIN
复杂的需求场景导致某些子查询场景不可避免。关于子查询,存在不少性能陷阱和认识误区值得关注。
1)MySQL子查询性能差的主要原因是子查询产生临时表吗?不完全正确,临时表并不可怕,一个完整的SQL语句,FROM/JOIN/GROUP/WHERE/ORDER等操作,不考虑索引优化的情况下,都有可能产生临时表。所以更严格的表述是在子查询产生的临时表上查询无法利用索引导致性能低下。
2)IN子查询往往性能不佳的真实原因是什么?是IN查询的临时表数据量太大,MySQL太弱,只能支持极少数量的IN子查询吗?不一定,显示列表IN(a,b,c)查询的性能并不算差,IN子查询真正的性能陷阱在于Mysql优化器往往将IN独立子查询优化成EXISTS相关子查询!所以当观察SELECT * FROM table1 WHERE table1.id IN(SELECT id FROM table2)的查询计划,会发现table2的查询为DEPEDENTSUBQUERY,原因其实是MySQL优化策略+历史原因。
3)子查询的性能一定弱于JOIN吗?未必,由于Mysql不支持Semi Join(注),所以在某些需要场景下,使用子查询性能优于JOIN。比如A表和B表一对多关系,如果仅仅想查询在B表中存在对应记录的A表记录,如果使用JOIN,需要用DISTINCT或者GROUP操作进行去重操作。使用关联子查询可以避免这部分开销。SELECT id FROM table1 WHERE EXISTS(SELECT table2.id FROM table2WHERE table2.id=table1.id)
关于Join,Mysql使用Nested Loop算法(注)。在典型的星型维度模型中,维度表数据量远小于事实表,JOIN操作往往是大小表连接,性能问题不大,这方面不多讲。结合前面提到的Covering Index,介绍一个利用JOIN提高分页效率的歪招:
分页往往需要用到LIMIT OFFSET,在偏移量很大的时候,比如LIMIT 100000,50,MySQL需要检索100050数据,性能严重下降。常见的处理方式是a)增加排序辅助列,将LIMIT转化为在辅助列上范围查找操作
b)应用层缓存机制 c)需求折中,没有人会翻到100000页。以上皆不灵的时候,可以选择Covering
Index+Join。
SELECT * FROM table1 INNER JOIN
(SELECT id FROM table1 ORDER BY indexed_col limit 100000,50) AS a
ON table1.id = a.id 这种方式效率较高,因为临时表a仅在索引上进行操作(Innodb索引叶子节点上存储了主键值),取得所需行id之后,再和完整的表进行Join获取其他所需列。
注:MySQL的著名分支MarioDB支持Semi
Join和Hash Join
其他
Pivoting&Unpivoting主要关注行列旋转变化,还可以用来对聚合数据进行格式化用于报表展现,在此不再复述MySQL与OLAP:分析型SQL查询最佳实践探索,布布扣,bubuko.com
热门排行
今日推荐
-
今日学堂免费版
版本:v3.7.2
大小:61.6MB
日期:2024-09-21
-
注安师好题库完整版
版本:v1.0.0
大小:67.34MB
日期:2024-09-21
-
campustop英语官方版
版本:v1.145
大小:187.6MB
日期:2024-09-21
-
优枢学堂安卓版
版本:v1.0.2
大小:53.49MB
日期:2024-09-21
-
快递通知宝官方版
版本:v1.4.0
大小:11.10MB
日期:2024-09-21
-
晶亮考驾照免费版
版本:v4.0.0
大小:45.79MB
日期:2024-09-21
热门手游
-
动物旅馆官方版
版本:v1.0.0
大小:20.74MB
日期:2024-09-20
-
异世界慢生活免费版
版本:v1.0.26
大小:1.10GB
日期:2024-09-20
-
心动小镇手机版
版本:v0.1.3
大小:1.92GB
日期:2024-09-20
-
猫语咖啡免费版
版本:v1.3.3
大小:575.18MB
日期:2024-09-20
-
模拟农场23安卓版
版本:v1.0
大小:0KB
日期:2024-09-20
-
极速飙车模拟手机版
版本:v1.0.1
大小:150.62MB
日期:2024-09-20
|