想把时间分成4段,并统计每段时间的总人数,达成以下效果
以下是我写的代码,感觉有点乱,有没有什么更好的办法改进一下呀?
WITH t1 AS (
# 分段整合
SELECT
SUM( IF ( log_hour IN ( 0, 1, 2, 3, 4, 5 ), num_user, NULL ) ) AS '0to6',
SUM( IF ( log_hour IN ( 6, 7, 8, 9, 10, 11 ), num_user, NULL ) ) AS '6to12',
SUM( IF ( log_hour IN ( 12, 13, 14, 15, 16, 17 ), num_user, NULL ) ) AS '12to18',
SUM( IF ( log_hour IN ( 18, 19, 20, 21, 22, 23 ), num_user, NULL ) ) AS '18to24'
FROM
(
# 提取hour
SELECT
HOUR ( log_hms ) AS log_hour,
COUNT( user_id ) AS num_user
FROM
login_rec
GROUP BY
log_hour ) a
)
# 列转行
SELECT
'0to6' AS period,
MAX( 0to6 ) AS num
FROM
t1
UNION
SELECT
'6to12' AS period,
MAX( 6to12 ) AS num
FROM
t1
UNION
SELECT
'12to18' AS period,
MAX( 12to18 ) AS num
FROM
t1
UNION
SELECT
'18to24' AS period,
MAX( 18to24 ) AS num
FROM
t1;