周总找我问个报表SQL实现逻辑的案例,废话不说给他看看。
原SQL:
SELECT d.tname 姓名,
d.spname 岗位,
d.sum_cnt 报单单量,
d.min_cnt 放款单量,
d.date 月份
FROM (SELECT *
FROM (SELECT a.zts_name tname,
a.sp_name spname,
CONVERT(ifnull(a.order_cnt, '0'), SIGNED) sum_cnt,
CONVERT(ifnull(b.order_cnt, '0'), SIGNED) min_cnt,
a.gmt_create DATE
FROM
(
SELECT
t.zts_name, t.sp_name, count( t.order_no ) AS order_cnt, DATE_FORMAT ( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
AAAA rto
INNER JOIN BBBB rts ON rto.id = rts.order_id
INNER JOIN CCCC zts ON rts.principal_id = zts.id
INNER JOIN DDDD ztj ON zts.id = ztj.staff_id
INNER JOIN EEEE ztr ON ztj.role_id = ztr.id
WHERE
rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) A
LEFT JOIN (
SELECT
t.zts_name,
t.sp_name,
count( t.order_no ) AS order_cnt,
DATE_FORMAT( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
AAAA rto
INNER JOIN BBBB rts ON rto.id = rts.order_id
INNER JOIN CCCC zts ON rts.principal_id = zts.id
INNER JOIN DDDD ztj ON zts.id = ztj.staff_id
INNER JOIN EEEE ztr ON ztj.role_id = ztr.id
WHERE
rto.state IN ( 4010, 4030 )
AND rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) b
ON A.zts_name = b.zts_name
AND A.gmt_create = b.gmt_create) c
ORDER BY c.date ASC,
CONVERT(c.sum_cnt, signed) DESC) d;
精简后的结果集:
周总的意思很简单,就是以 月份 来分组,求出 报单数量 在不同月份中的中位数就行。
这道题读者不需要过多关注原来的SQL是如何写的,只需要把重点放在如何取 报单数量 的中位数即可。
中位数:在一个数列中,如果按照大小顺序排列,中位数就是位于中间的那个数。
这道SQL的解题思路其实很简单:
1、使用 DENSE_RANK 开窗函数对 月份分组 ,然后对 报单数量排序,注意:不能使用 ROW_NUMBER() 和 RANK() 进行排序,这两个函数一个会给连续的序号,另外一个会跳号。
2、然后对 DENSE_RANK 开窗函数 的排序结果进行 max 开窗取每个月的最大序号。
3、最后使用 每个月的最大序号除以2 就可以得出不同月份的中位数。
最终SQL代码:
SELECT *
FROM (
/* FLOOR(j.最大的编号 / 2) 中位数编号
最后使用 每个月的最大序号除以2 就可以得出不同月份的中位数。
*/
SELECT j.姓名,
j.岗位,
j.报单单量,
j.放款单量,
j.月份,
j.编号,
FLOOR(j.最大的编号 / 2) 中位数编号
FROM (
/* MAX(x.rn) OVER (PARTITION BY x.月份 ) 最大的编号
然后对 DENSE_RANK 开窗函数 的排序结果进行 max 开窗取每个月的最大序号
*/
SELECT x.姓名,
x.岗位,
x.报单单量,
x.放款单量,
x.月份,
x.rn 编号,
MAX(x.rn) OVER (PARTITION BY x.月份 ) 最大的编号
FROM (
/* DENSE_RANK() OVER (PARTITION BY d.date ORDER BY d.sum_cnt) rn
对月份分组 ,然后对报单数量排序
*/
SELECT
d.tname 姓名,
d.spname 岗位,
d.sum_cnt 报单单量,
d.min_cnt 放款单量,
d.date 月份,
DENSE_RANK() OVER (PARTITION BY d.date ORDER BY d.sum_cnt) rn
FROM (
/* 以下SQL为原始SQL */
SELECT *
FROM (
SELECT a.zts_name tname,
a.sp_name spname,
CONVERT(ifnull(a.order_cnt, '0'), SIGNED) sum_cnt,
CONVERT(ifnull(b.order_cnt, '0'), SIGNED) min_cnt,
a.gmt_create DATE
FROM
(
SELECT
t.zts_name, t.sp_name, count( t.order_no ) AS order_cnt, DATE_FORMAT ( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
AAAA rto
INNER JOIN BBBB rts ON rto.id = rts.order_id
INNER JOIN CCCC zts ON rts.principal_id = zts.id
INNER JOIN DDDD ztj ON zts.id = ztj.staff_id
INNER JOIN EEEE ztr ON ztj.role_id = ztr.id
WHERE
rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) A
LEFT JOIN (
SELECT
t.zts_name,
t.sp_name,
count( t.order_no ) AS order_cnt,
DATE_FORMAT( t.gmt_create, '%Y-%m' ) gmt_create
FROM
(
SELECT
zts.`name` AS zts_name,
ztr.`name` AS sp_name,
rto.order_no,
rto.gmt_create AS gmt_create
FROM
AAAA rto
INNER JOIN BBBB rts ON rto.id = rts.order_id
INNER JOIN CCCC zts ON rts.principal_id = zts.id
INNER JOIN DDDD ztj ON zts.id = ztj.staff_id
INNER JOIN EEEE ztr ON ztj.role_id = ztr.id
WHERE
rto.state IN ( 4010, 4030 )
AND rts.role = '7'
AND ztr.`description` = '客户经理'
) t
GROUP BY
t.zts_name
) b
ON A.zts_name = b.zts_name
AND A.gmt_create = b.gmt_create) c
ORDER BY c.date ASC,
CONVERT(c.sum_cnt, signed) DESC) d) x) j) g
WHERE g.编号 = g.中位数编号;
最后可以看到该SQL已经改写成功,可以列出不同月份 达到中位数的销售姓名的单量。
相关内容