gpt4 book ai didi

MySQL 查询按 10 天周期生成报告

转载 作者:行者123 更新时间:2023-11-29 21:52:17 26 4
gpt4 key购买 nike

所以我有一张这样的 table

id  | user_id | point | created_at
-------------------------------------------
1 | 1 | 10 | 2015-08-03 00:08:25
2 | 3 | 20 | 2015-08-01 00:08:25
3 | 4 | 30 | 2015-08-13 00:08:25
4 | 3 | 10 | 2015-08-25 00:08:25
5 | 2 | 20 | 2015-09-02 00:08:25
6 | 1 | 10 | 2015-09-14 00:08:25
7 | 4 | 50 | 2015-09-22 00:08:25
8 | 2 | 80 | 2015-09-30 00:08:25
9 | 1 | 30 | 2015-10-02 00:08:25
10 | 5 | 90 | 2015-10-02 00:08:25

我可以生成一个查询来得到这样的结果吗?

start_date | end_date   | total_point_100_or_more | total_point_less_than_100
------------------------------------------------------------------------------
2015-08-01 | 2015-08-10 | 3 User | 7 User
2015-08-11 | 2015-08-20 | 8 User | 5 User
2015-08-21 | 2015-08-30 | 10 User | 5 User
2015-08-31 | 2015-09-09 | 4 User | 6 User

我现在没有任何代码行,因为我完全不知道从哪里开始。

最佳答案

我还没有对此进行测试,并且无法测试,因为您的示例答案与您的示例数据不符,但类似于以下内容的内容应该可以工作。在这里,我假设您想知道用户(在每个时间段内)总得分高于或低于 100 分的可能性有多少。

SELECT  start_date,
DATEADD(day, 9, start_date) as end_date,
SUM(CASE WHEN period_points >= 100 THEN 1 ELSE 0 END) as total_point_100_or_more,
SUM(CASE WHEN period_points < 100 THEN 1 ELSE 0 END) as total_point_less_than_100
FROM (
SELECT start_date,
user_id,
SUM(point) as period_points
FROM (
SELECT DATEADD(
day,
DATEDIFF(day, '2015-08-01 00:00:00', created_at)/10*10,
'2015-08-01 00:00:00'
) as start_date,
id,
user_id,
point
FROM points
) AS p
GROUP BY start_date, user_id
) as q
GROUP BY start_date

请注意,我没有将整数计数转换为文本并附加“用户”,如示例中所示。

关于MySQL 查询按 10 天周期生成报告,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33514792/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com