时间戳

首页 > 解决方案 > 从日期范围获取连续的月份和天数？

sql - 从日期范围获取连续的月份和天数？

问题描述

所以假设我有一张这样的桌子：

订阅者 ID package_id package_start_date 包裹结束日期 package_price_per_day
1081 231 2014-01-13 2014-12-31 3 美元。
1084 231 2014-03-21 2014-06-05 3 美元
1086 235 2014-06-21 2014-09-09 4 美元

现在我想要基于 2014 年每个月的总收入的前 3 个包的结果。注意：例如包 231 的收入应该计算为 1 月的 18 天 * 3 美元 + 2 月的 28 天 * 3 美元 + ... 。 等等。对于第二行，计算将与第一行相同（3 月的 9 天 * 3 美元 + 4 月的 30 天 * 3 美元 ....）结果包应根据月份分组并根据总收入显示排名。

样本结果：

月 Package_id 收入 秩
简 231. 69499 1.
简。 235. 34345。 2.
简。 238. 23455。 3.
二月 231. 89274 1.

我写了一个查询来过滤日期，以便我在 2014 年全年获得活跃订阅者（因为最初有来自不同年份的值），它显示了问题中的第一个表，但我不确定如何打破月份和之后的几天。

select subscriber_id, package_id, package_start_date, package_end_date
from  (
    select subscriber_id, package_id
        , case when year(package_start_date) < '2014' then package_start_date = '01-Jan-2014' else package_start_date end as package_start_date
        , case when year(package_start_date) > '2014' then package_end_date = '31-Dec-2014' else package_start_date end as package_end_date
        , price_per_day 
    from subscription
)  a
where year(package_start_date) = '2014' and year(package_end_date) = '2014'

请不要强调语法 - 我只是想了解 SQL 中的逻辑方法。

标签： sqlsql-servertsql

解决方案

假设您有一个表，该表是名为 d 的列中唯一日期的列表，并且该表称为 d

然后做起来相对微不足道

SELECT * 
FROM t
  INNER JOIN d on d.d >= t.package_start_date AND d.d < t.package_end_date

假设您将 1 月 1 日的开始日期和 1 月 2 日的结束日期分类为 1 天。如果你是两个，使用 <=

这将导致您的包裹行乘以天数，因此 1 月 1 日和 1 月 11 日的开始和结束天数意味着该行重复 10 次。每一行的 dd 日期都不同，您可以从 dd 中提取月份，然后对其进行分组，以便为​​您提供每个包裹每个月的总计

假设您已将上面的查询 CTEd 为 x，就像

SELECT DATEPART(month, x.dd), --the d.d date
    package_id,
    SUM(revenue)
FROM x
GROUP BY DATEPART(month, x.dd), package_id

因为 T 中的行在连接到 d 时会通过笛卡尔爆炸重复，因此您可以安全地对它们进行分组或聚合，以使它们恢复为每个包每月的单个值。如果您有超过一年的包裹，您还应该在日期部分年份分组，以避免混淆从例如 2020 年 1 月到 2021 年 2 月的包裹的月份（它们会停留两个 jan 和两个 feb）

然后你需要做的就是添加收入的排名，看起来它会在第一步进入，比如

RANK(DATEDIFF(DAY, start, end)*revenue) OVER(PARTITION BY package_id)

我想我理解正确，您在整个期间而不是按月对总收入进行排名。. 也请查看排名和密集排名之间的差异，因为您可能想要密集排名

推荐阅读