时间戳

首页 > 解决方案 > 日期窗口功能

sql - 日期窗口功能

问题描述

不知道如何解决问题。也许您可以显示正确的方向或提供链接。我有一张桌子：

    id  Date
    23  01.01.2020
    23  03.01.2020
    23  04.01.2020
    56  07.01.2020
    56  08.01.2020
    87  11.01.2020
    23  12.01.2020
    23  18.01.2020

我想聚合数据（id，Date_min）并添加这样的新列：

id  Date_min        Date_new
23  01.01.2020  07.01.2020
56  07.01.2020  11.01.2020
87  11.01.2020  12.01.2020
23  12.01.2020  18.01.2020

在 Data_new 列中，我想查看下一个用户的第一个日期。如果没有下一个用户，添加用户的最大日期

标签： sqlpostgresql

解决方案

LEAD 会给你下一个日期，但我们也有你的 ID 重复的轻微问题，所以我们需要一些东西来使第二个 23 与第一个不同。为此，我想我们可以建立一个计数器，每次 ID 更改时都会打勾：

with a as(        
    select '23' as id, '01.01.2020' as "date" union all
    select '23' as id, '03.01.2020' as "date" union all
    select '23' as id, '04.01.2020' as "date" union all
    select '56' as id, '07.01.2020' as "date" union all
    select '56' as id, '08.01.2020' as "date" union all
    select '87' as id, '11.01.2020' as "date" union all
    select '23' as id, '12.01.2020' as "date" union all
    select '23' as id, '18.01.2020' as "date"
), b as (
  SELECT *, LAG(id) OVER(ORDER BY "date") as last_id FROM a
), c AS(
  SELECT *, 
  LEAD("date") OVER(ORDER BY "date") as next_date, 
  SUM(CASE WHEN last_id <> id THEN 1 ELSE 0 END) OVER(ORDER BY "date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) id_ctr
  FROM b
)

SELECT id, MIN("date"), MAX(next_date)
FROM c
GROUP BY id, id_ctr

我没有 PG 实例来测试它，但它在 SQLS 中工作，我很确定 PG 支持 SQLS 所做的一切——这里没有任何 SQLS 特定的东西

  • a代替您的表格 - 您可以将其从查询中删除，然后直接 dawith b as (select... from yourtablenamehere)
  • b计算前一个ID；我们将使用它来检测当前行和上一行之间的 id 是否已更改。如果它发生变化，我们将输入 1 否则为 0。当这些总和作为运行总数时，这实际上意味着每次 ID 更改时计数器都会打勾，因此我们可以按此计数器和 ID 分组以拆分我们的两个23分开。我们需要单独这样做，因为窗口函数不能嵌套
  • c采用 last_id 并计算运行总数。它还next_date使用一个简单的窗口函数来执行此操作，该函数从下一行（按日期排序的行）中提取日期。这ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW在技术上是不必要的，因为它是 SUM OVER ORDERBY 的默认操作，但我发现如果需要，明确有助于记录/更改
  • 那么所需要做的就是选择 id、最小日期和最大 next_date，但也将计数器扔在那里以拆分23s - 您可以按比您选择的更多的列进行分组，但不能反过来

推荐阅读