mariadb SQL如何对共享时间顺序的元素序列进行分组

我有一组包含开始日期和停止日期的记录，如下所示：
| 识别码|已启动|停止|
| - -| - -| - -|
| 一个|2017年8月14日| 2017年10月22日|
| 2个|2017年10月23日|2017年12月12日|
| 三个|2019年1月28日|2019年2月21日-〉|
| 四个|某日期|空值|
| 五个|2020年9月8日| 2020年9月14日|
| 六个|2020年9月15日|***2020年10月14日|
| 七个|- 〉2019年2月22日|2019年3月18日|
我需要合并那些按时间顺序突出显示的日期序列，这些日期序列遵循以下规则（停止=开始+一天）。
结果应如下所示：
| 识别码|已启动|停止|
| - -| - -| - -|
| 一个|2017年8月14日|2019年3月18日|
| 2个|某日期|空值|
| 三个|2020年9月8日|***2020年10月14日|
我的做法：创建3个新列，并检查每个条目x与其前一个条目（x-1）和下一个条目（x +1），如果其中任何一个在-1和+1之间，则将它们分组：

update date_table as t
    set group_num_before = (select date_difference
                            from (SELECT g1.id, DATEDIFF(g1.start_dt, g2.stop_dt) AS date_difference
                                  FROM date_table g1
                                           INNER JOIN
                                       date_table g2 ON g2.id = g1.id - 1) as groupNum
                            where t.id = groupNum.id),
        group_num_after  = (select date_difference
                            from (SELECT g1.id, DATEDIFF(g1.stop_dt, g2.start_dt) AS date_difference
                                  FROM date_table g1
                                           INNER JOIN
                                       date_table g2 ON g2.id = g1.id + 1) as groupNum
                            where t.id = groupNum.id)
    where true;

    update date_table as g1
        INNER JOIN
        date_table g2 ON g2.id = g1.id
    set g1.group_num = IF(g1.group_num_before in (-1, 1) OR
                          g1.group_num_after in (-1, 1), -1, g1.group_num_before)
    where g1.A = g2.B;

这导致：
| 识别码|已启动|停止|组编号|
| - -| - -| - -| - -|
| 一个|2017年8月14日| 2017年10月22日|-1个|
| 2个|2017年10月23日|2017年12月12日|-1个|
| 三个|2017年12月13日|2019年2月21日-〉|-1个|
| 四个|某日期|空值|零值|
| 五个|2020年9月8日| 2020年9月14日|-1个|
| 六个|2020年9月15日|***2020年10月14日|-1个|
| 七个|- 〉2019年2月22日|2019年3月18日| -1个|
但是，这样做会将所有已排序的记录放在同一个组中，因为它们也是按时间顺序出现的，这会导致将它们分组在一起时产生不正确的结果。
任何想法都将不胜感激提前感谢。

使用LAG查看上一个停止点并标记所有间隙。然后计算间隙以获得组编号。

select started, stop, count(flag) over (order by started) as grp
from
(
  select 
    started, stop,
    case when lag(stop) over (order by started) = started - interval 1 day then null else 1 end as flag
  from date_table
) with_flags
order by started;

如果要为每个组编号获取一个结果行，请聚合以上结果：

select
  grp,
  min(started) as started,
  case when count(*) = count(stop) then max(stop) end as stop
from
(
  select started, stop, count(flag) over (order by started) as grp
  from
  (
    select 
      started, stop,
      case when lag(stop) over (order by started) = started - interval 1 day then null else 1 end as flag
    from date_table
  ) with_flags
) grouped
group by grp
order by grp;

演示：https://dbfiddle.uk/jKlHZ09P

mariadb SQL如何对共享时间顺序的元素序列进行分组

1条答案

相关问题

热门标签

最新问答