mysql统计查询和一行转多行（列转行）--- help_topic

原创已于 2022-12-31 12:41:15 修改 · 2.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

于 2022-12-31 12:33:04 首次发布

本文介绍了如何使用MySQL的help_topic表结合日期函数实现按天统计事件数据，包括page_view、view_content、add_to_cart和complate_payment，同时演示了如何生成缺失日期的数据点，确保完整的时间轴覆盖。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql.help_topic
help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有656条数据，超过这个数字，我们就需要己自定义一张表。

1、查询每个渠道对应的30天内的page_view、view_content、add_to_cart、complate_payment的总数

SELECT dc.channel_name,
SUM(CASE WHEN del.event_name = 'page_view' THEN 1 ELSE 0 END) AS page_view,
SUM(CASE WHEN del.event_name = 'view_content' THEN 1 ELSE 0 END) AS view_content,
SUM(CASE WHEN del.event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,
SUM(CASE WHEN del.event_name = 'complate_payment' THEN 1 ELSE 0 END) AS complate_payment

FROM da_channel AS dc LEFT JOIN da_event_log AS del
ON dc.id = del.channel_id
AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(del.created_at)
GROUP BY dc.id

---on后面直接跟and，不是where后面跟and

如图所示：

重点：有时我们在生成一些时间轴类似的数据时，要求数据库不管有没有指定天的数据，都要生成该时间节点，可用mysql.help_topic来解决此类问题，通过序列和日期函数相结合来满足我们的业务需求。

列传行：
SELECT DATE_FORMAT(DATE_ADD('2022-12-01', INTERVAL(CAST(help_topic_id AS SIGNED INTEGER)+ 0) DAY),'%Y-%m-%d') date_day
FROM mysql.help_topic
WHERE help_topic_id < DAY(LAST_DAY('2022-12-01'))
ORDER BY help_topic_id

或者
SELECT
   DATE( DATE_ADD( '2022-12-01', INTERVAL @s DAY ) ) AS date,
   '异常总条数' as e_name,
   @s := @s + 1 AS `index`
FROM
   mysql.help_topic,
   ( SELECT @s := 0 ) temp
WHERE
   @s <= DATEDIFF('2022-12-31','2022-12-01')

数据如下：

2、查询出12月整个月份，统计相关的值sql语句：

SELECT a.date_day,
SUM(CASE WHEN del.event_name = 'complate_payment' THEN 1 ELSE 0 END) AS shopify,
SUM(CASE WHEN del.state = 0 THEN 1 ELSE 0 END) AS success,
SUM(CASE WHEN del.state = 1 THEN 1 ELSE 0 END) AS failures
FROM (
SELECT DATE_FORMAT(DATE_ADD('2022-12-01', INTERVAL(CAST(help_topic_id AS SIGNED INTEGER)+ 0) DAY),'%Y-%m-%d') date_day
FROM mysql.help_topic
WHERE help_topic_id < DAY(LAST_DAY('2022-12-01'))
ORDER BY help_topic_id
) a
LEFT JOIN da_event_log AS del ON a.date_day = DATE_FORMAT(del.created_at, '%Y-%m-%d')
AND del.created_at < DATE_ADD(DATE_FORMAT('2022-12-01', '%Y-%m-%d'), INTERVAL 1 MONTH)
AND del.created_at >= '2022-12-01'
GROUP BY a.date_day