56,687
社区成员
发帖
与我相关
我的任务
分享
SELECT t1.c c1, t2.c c2, t1.c/t2.c rate, t2.pl, t2.`day` date FROM
(SELECT sum(row_num) c, product_line pl, `day`
FROM nt_stat_num_product_line
WHERE `day`>='20140921' AND `day`<='20141021' AND `name`='SNOOPY_HOST_COUNT'
GROUP BY product_line, `day`) t1
JOIN
(SELECT sum(row_num) c, product_line pl, `day`
FROM nt_stat_num_product_line
WHERE `day`>='20140921' AND `day`<='20141021' AND `name`='ALL_SNOOPY_HOST_COUNT'
GROUP BY product_line, `day`) t2
ON t1.pl=t2.pl AND t1.`day`=t2.`day`
ORDER BY date DESC, rate ASC;
SELECT sum(row_num) c, product_line pl, `day`
FROM nt_stat_num_product_line
WHERE `day`>='20140921' AND `day`<='20141021' AND `name`='SNOOPY_HOST_COUNT'
GROUP BY product_line, `day`
结果几千行,随便贴几行:
+------+---------------------------------+----------+
| c | pl | day |
+------+---------------------------------+----------+
| 633 | xx部门 | 20141001 |
| 633 | xx部门 | 20141002 |
| 633 | xx部门 | 20141003 |
| 633 | xx部门 | 20141004 |
| 633 | xx部门 | 20141005 |
+------+---------------------------------+----------+
执行计划(这里没针对group by 加组合索引 但是子查询本身已经够快了)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: nt_stat_num_product_line
type: ref
possible_keys: idx_nt_stat_name,idx_nt_stat_day
key: idx_nt_stat_name
key_len: 195
ref: const
rows: 2783
Extra: Using where; Using temporary; Using filesort