时间戳

首页 > 解决方案 > 使用 CustID 和每日金额和 MTD 进行分组查询

sql - 使用 CustID 和每日金额和 MTD 进行分组查询

问题描述

我有以下数据：

ID 用户名 成本 时间
1 测试1 1 2021-05-22 11:48:36.000
2 测试2 2 2021-05-20 12:55:22.000
3 测试3 5 2021-05-21 00:00:0-0.000

我想用一张表中的每日数字和一个月至今的数字来计算用户名的成本

我有以下

SELECT 
    username,
    COUNT(*) cost
FROM
    dbo.sent
WHERE
    time BETWEEN {ts '2021-05-01 00:00:00'} AND {ts '2021-05-22 23:59:59'}
GROUP BY 
    username
ORDER BY
    cost DESC;

这将返回每月数据并将时间更改为 '2021-05-22 00:00:00'} 和 {ts '2021-05-22 23:59:59'} 将给出每日但我想要一个表一起显示每日和 MTD

用户名 日常的 MTD
测试1 1 1012
测试2 2 500
测试3 5 22

任何帮助或指针都会很棒，我猜我需要一个临时表，然后使用 MTD 范围再次运行更新用户名相同的临时表，然后导出和删除 - 但我不知道从哪里开始。

标签： sqlsql-servergroup-by

解决方案

首先，您需要每个用户和日期一行，而不仅仅是每个用户一行。

其次，你应该修正你的日期算术，这样你就不会错过一秒钟。

然后，您可以使用聚合和窗口函数：

SELECT username, CONVERT(DATE, time) as dte,
       COUNT(*) as cost_per_day,
       SUM(COUNT(*)) OVER (PARTITION BY username ORDER BY CONVERT(DATE, time)) as mtd
FROM dbo.sent s
WHERE time >=  '2021-05-01' AND
      time < '2021-05-23'
GROUP BY username, CONVERT(DATE, time)
ORDER BY username, dte;

您可以在 SQL 教程（或者，咳咳，一本关于 SQL 的书）或文档中了解有关窗口函数的更多信息。

编辑：

如果您只想要最近的日期和 MTD，那么您可以过滤上述查询以获得最近的日期或使用条件聚合：

SELECT username,
       SUM(CASE WHEN CONVERT(DATE, time) = '2021-05-22' THEN 1 ELSE 0 END) as cost_per_most_recent_day,
       COUNT(*) as MTD
FROM dbo.sent s
WHERE time >=  '2021-05-01' AND
      time < '2021-05-23'
GROUP BY username
ORDER BY username;

而且，您实际上可以使用当前日期来表达查询，因此不必对其进行硬编码。对于这个版本：

SELECT username,
       SUM(CASE WHEN CONVERT(DATE, time) = CONVERT(DATE, GETDATE()) THEN 1 ELSE 0 END) as cost_per_most_recent_day,
       COUNT(*) as MTD
FROM dbo.sent s
WHERE time >=  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND
      time < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
GROUP BY username
ORDER BY username;

推荐阅读