时间戳

首页 > 解决方案 > 在每个月的第一天获取列的最新值

sql - 在每个月的第一天获取列的最新值

问题描述

我有一张这样的桌子：

 id  col2   modified date
 1  red     1/7/2019
 1  green   2/7/2019
 1  blue    3/7/2019
 2  green   1/12/2019
 2  blue    3/02/2019
 2  red     4/19/2019
 3  red     12/12/2018
 3  green   02/10/2019

我需要创建一个查询，如下所示：

假设我在 2019 年 4 月运行它，那么输出应该如下所示

id  col2(first day of every month)  col4
1   1/1/2019                        NULL
1   2/1/2019                        red
1   3/1/2019                        green
1   4/1/2019                        blue
2   1/1/2019                        NULL
2   2/1/2019                        green
2   3/1/2019                         green
2   4/1/2019                         blue
3   1/1/2019                         red
3   2/1/2019                         red
3   3/1/2019                        green
3   4/1/2019                        green

所以基本上我需要知道每个 ID 在每个月的第一天 col2 的值。例如：对于 id '1'，表 1 中 col2 没有最后一个值，因为它是在 1 月 7 日修改的。所以 col4 值在第二个表中将为 NULL。但在 2 月 1 日它将显示为红色，因为它是最多的该日期的最新值。同样，此逻辑如何适用于其他 id。我们需要在每个月的 1 日追踪每个 id 的 col2 的最新值。

我尝试了多种方法，但无法同时处理所有场景。

标签： sqlsql-server

解决方案

Rextester 演示：

您的问题中有很多未知数：我们怎么知道限制仅 4 个月？在给定的月份中是否有多种颜色变化，您想列出每一种吗？因此，我根据您定义的预期结果做了一些假设。但是，我相信您对 ID 1 上的第 4 个条目的预期结果有错误。我认为它不会是绿色的；但应该是蓝色的。如果这个假设是错误的，我找不到任何符合您预期结果的模式。

我认为解决这方面的知识CROSS JOIN是OUTER APPLY需要的。了解如何使用recursive CTE(CTE = COMMON TABLE EXPRESSION) 获取某个范围内的日期也可能会有所帮助；取决于您的长期需求；或者，正如评论中所建议的，只有一个可以从中提取的“日期”表。

因此，对于以下内容：

  • CTE是您的数据表
  • Dates是一个表格，其中包含有问题的 4 个月的每个月的第一天。该数据集可以根据您的数据生成，最后我提供了如何执行此操作的链接。
  • CROSS join 用于确保每个 ID 有 4 个月的时间，以防数据出现缺口
  • OUTER APPLY 用于获取记录月初之前的最新颜色变化；如果存在这样的记录。我们使用外部应用，因为这样的记录可能不存在。与条目 1 一样
--CTE is your sample data
with cte (id,  col2,   modifieddate) as 
    (SELECT 1,   'red',  cast('20190107' as date)  UNION ALL
     SELECT 1,   'green',cast('20190207' as Date) UNION ALL
     SELECT 1,   'blue',cast('20190307' as Date) UNION ALL    
     SELECT 2,   'green',cast('20190112' as Date) UNION ALL   
     SELECT 2,   'blue',cast('20190302' as Date) UNION ALL    
     SELECT 2,   'red',cast('20190419' as Date) UNION ALL     
     SELECT 3,   'red',cast('20181212' as Date) UNION ALL     
     SELECT 3,   'green',cast('20190210' as Date)),
-- You didn't define how you know where to start /stop so I just based this on 
-- your results which only went for four months Jan-April of 2019.
  Dates as (SELECT cast('20190101' as date) FirstofMonth  UNION ALL
               SELECT cast('20190201' as date) FirstofMonth  UNION ALL
               SELECT cast('20190301' as date) FirstofMonth  UNION ALL
               SELECT cast('20190401' as date) FirstofMonth )
--This is really the steps needed
--Cross join the dates to your unique ID list so we get 1 date per ID entry  This fills in the missing dates if any exist.
-- Then we use an outer apply to get the most recent color change before that first of month for that ID.  We use a correlated query to only get the most recent color change before the modified date on the record in question.  Thus we have a Top 1 and order by modifed date desc.
     SELECT Z.iD, A.FirstofMonth, Col2 as Col4
     FROM Dates A
     CROSS JOIN (SELECT DISTINCT ID FROM CTE) Z
     OUTER APPLY(SELECT TOP 1 * FROM CTE B
                 WHERE Z.ID = B.ID
                   and B.ModifiedDate<=A.FirstOfMonth
                 ORDER BY B.ModifiedDate desc) X

给我们：

+----+----+---------------------+-------+
|    | iD |    FirstofMonth     | Col4  |
+----+----+---------------------+-------+
|  1 |  1 | 01.01.2019 00:00:00 | NULL  |
|  2 |  1 | 01.02.2019 00:00:00 | red   |
|  3 |  1 | 01.03.2019 00:00:00 | green |
|  4 |  1 | 01.04.2019 00:00:00 | blue  | <-- I think you have a error in expected results.
|  5 |  2 | 01.01.2019 00:00:00 | NULL  |
|  6 |  2 | 01.02.2019 00:00:00 | green |
|  7 |  2 | 01.03.2019 00:00:00 | green |
|  8 |  2 | 01.04.2019 00:00:00 | blue  |
|  9 |  3 | 01.01.2019 00:00:00 | red   |
| 10 |  3 | 01.02.2019 00:00:00 | red   |
| 11 |  3 | 01.03.2019 00:00:00 | green |
| 12 |  3 | 01.04.2019 00:00:00 | green |
+----+----+---------------------+-------+

现在，您可能需要一个动态日期生成器来获取结果中日期之间的每个月的第一天；这些示例可以在其他堆栈问题中找到，例如：获取 SQL Server 中两个日期之间的所有日期

或 https://social.msdn.microsoft.com/Forums/windowsdesktop/en-US/f648408f-bf91-4f84-8f69-94df8506d4a5/getting-all-months-start-and-end-dates-between-two-dates ?forum=transactsql

两者都使用递归 CTE 和开始/结束日期来生成范围内的日期。第一个做所有日期，第二个只做月份的第一天和最后一天。如果您将基表的最小值/最大值用于日期范围，我认为第二个日期就足够了。

推荐阅读