postgresql 插入具有预期逻辑的查询以将表分叉

mznpcxlj 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(97)

我有一张table

CREATE TABLE consumer_occurrence_restoration_times (
    consumer_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

其中数据被

INSERT INTO consumer_occurrence_restoration_times (consumer_id, start_time, end_time)
VALUES
    (1, '2023-09-24 20:00:00', '2023-09-25 12:00:00'),
    (2, '2023-09-24 21:00:00', '2023-09-25 13:00:00'),
    (1, '2023-09-26 20:00:00', '2023-09-28 02:00:00'),
    (3, '2023-09-25 19:00:00', '2023-09-26 10:00:00'),
    (4, '2023-09-25 21:30:00', '2023-09-27 14:00:00'),
    (5, '2023-09-25 21:30:00', '2023-09-25 22:00:00')

我想使用查询将此数据插入另一个表tbl_bifuraction，以便它插入一天的数据，即start_time与end_time发生在同一天，而不是在第二天的00：00：00之后。

CREATE TABLE tbl_bifurcation (
    consumer_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
  isdaydiff INT
);

consumer_occurrence_restoration_times表中的数据如下：

consumer_id   start_time            end_time
1             2023-09-24 20:00:00   2023-09-25 00:00:00
2             2023-09-24 21:00:00   2023-09-25 13:00:00
1             2023-09-26 20:00:00   2023-09-28 02:00:00
3             2023-09-25 19:00:00   2023-09-26 10:00:00
5             2023-09-25 21:30:00   2023-09-25 22:00:00

插入查询将consumer_occurrence_restoration_times表中的数据插入到另一个名为tbl_bifurcation的表中。但是，它需要将时间间隔拆分为单独的几天，并将它们作为单独的行插入，考虑从start_time到end_time的一天。此外，如果某些数据的开始时间为'2023-09-26 20：00：00'，结束时间为'2023-09-27 20：00：00'，则isdaydiff为1，但当结束时间为'2023-09-27 00：00：00'时，它保持为0。例如，如果start_time是'2023-09-26 20：00：00'，end_time是'2023-09-28 02：00：00'，我想在tbl_bifuraction中插入这样的行：
| 消费者标识|开始时间（_T）|结束时间|伊斯代夫|
| --|--|--|--|
| 1 |2023-09-24 20：00：00| 2023-09-25 00：00：00| 0 |
| 2 |2023-09-24 21：00：00| 2023-09-25 00：00：00| 1 |
| 2 |2023-09-25 00：00：00| 2023-09-25 13：00：00| 1 |
| 1 |2023-09-26 20：00：00| 2023-09-27 02：00：00| 1 |
| 1 |2023-09-27 02：00：00| 2023-09-28 00：00：00| 1 |
| 1 |2023-09-28 00：00：00| 2023-09-28 02：00：00| 1 |
| 3 |2023-09-25 19：00：00| 2023-09-26 00：00：00| 1 |
| 3 |2023-09-26 00：00：00| 2023-09-26 10：00：00| 1 |
| 5 |2023-09-25 21：30：00| 2023-09-25 22：00：00| 0 |
这里，当分叉完成时，isdaysdiff = 1（在除第一行和最后一行之外的所有行中），而不是在所有情况下。

isdaydiff更新条件

isdaydiff = 0仅在开始日期和结束日期分叉时使用，并且仅在开始日期和结束日期不在同一天时分叉。与consumer_id无关。每一行，无论任何数据是一个单独的行，没有任何问题，与前一行。但同一个消费者只有在下一行的start_date大于或等于上一行的end_date且消费者id相同时才会有不同的行。
为bifuracted_query添加了fiddle链接

postgresql

来源：https://stackoverflow.com/questions/77177202/insert-query-with-expected-logic-to-bifurcate-table

2条答案

按热度按时间

cclgggtu1#

您可以先插入不需要拆分的记录，然后用需要拆分的选择UNION ALL。使用generate_series()来生成中间日期。Demo：

INSERT INTO tbl_bifurcation
SELECT *, 0 as isdaydiff
FROM consumer_occurrence_restoration_times
WHERE start_time::date=end_time::date     --the ones
OR (  end_time::date=(start_time::date+1) --that don't require
    AND end_time::time='00:00:00'::time)  --splitting
UNION ALL  
SELECT consumer_id,
  added_date+case when added_date::date=start_time::date 
                  then start_time::time::interval 
                  else '0s'::interval end,
  added_date+case when added_date::date=end_time::date 
                  then end_time::time::interval 
                  else '1 day'::interval end,
  1 as isdaydiff
FROM (
   SELECT *,generate_series(start_time::date,
                            end_time::date,
                            '1 day'::interval) added_date
   FROM consumer_occurrence_restoration_times
   WHERE start_time::date<>end_time::date       --opposite condition
   AND NOT (end_time::date=(start_time::date+1) --so the ones to split
            AND end_time::time='00:00:00'::time) ) a;

SELECT * FROM tbl_bifurcation
ORDER BY 1,2,3;

| 消费者标识|开始时间（_T）|结束时间|伊斯代夫|
| --|--|--|--|
| 1 |2023-09-24 20：00：00| 2023-09-25 00：00：00| 0 |
| 1 |2023-09-26 20：00：00| 2023-09-27 00：00：00| 1 |
| 1 |2023-09-27 00：00：00| 2023-09-28 00：00：00| 1 |
| 1 |2023-09-28 00：00：00| 2023-09-28 02：00：00| 1 |
| 2 |2023-09-24 21：00：00| 2023-09-25 00：00：00| 1 |
| 2 |2023-09-25 00：00：00| 2023-09-25 13：00：00| 1 |
| 3 |2023-09-25 19：00：00| 2023-09-26 00：00：00| 1 |
| 3 |2023-09-26 00：00：00| 2023-09-26 10：00：00| 1 |
| 5 |2023-09-25 21：30：00| 2023-09-25 22：00：00| 0 |

+case有条件地恢复一系列时间戳的开始和结束的时间部分。中间的一切都只能用午夜。
1.您还可以使用extract()从timestamp获取日期、时间等信息。因为我只需要日期和时间，而date和time都有自己的类型，并带有预定义的向上和向下转换，所以我使用了它们。

赞(0）回复(0）举报 2023-10-18

bq3bfh9z2#

您可以通过将时间间隔划分为不同的日期并使用公共表表达式（CTE）和UNION ALL运算符的组合计算isdaydiff标志来实现这一点。尝试以下SQL查询，将数据插入tbl_bifurcation表;

WITH RECURSIVE DateRange AS (
  SELECT
    consumer_id,
    start_time,
    CASE
      WHEN DATE_TRUNC('day', start_time) = DATE_TRUNC('day', end_time)
        THEN end_time
      ELSE DATE_TRUNC('day', start_time) + INTERVAL '1 day' - INTERVAL '1 microsecond'
    END AS end_time,
    0 AS isdaydiff
  FROM consumer_occurrence_restoration_times
  UNION ALL
  SELECT
    consumer_id,
    CASE
      WHEN DATE_TRUNC('day', start_time) = DATE_TRUNC('day', end_time)
        THEN end_time + INTERVAL '1 microsecond'
      ELSE DATE_TRUNC('day', start_time) + INTERVAL '1 day'
    END AS start_time,
    end_time,
    CASE
      WHEN DATE_TRUNC('day', start_time) = DATE_TRUNC('day', end_time)
        THEN 0
      ELSE 1
    END AS isdaydiff
  FROM DateRange
  WHERE start_time < end_time
)
INSERT INTO tbl_bifurcation (consumer_id, start_time, end_time, isdaydiff)
SELECT consumer_id, start_time, end_time, isdaydiff
FROM DateRange
ORDER BY consumer_id, start_time;

在这个查询中，我使用递归CTE DateRange将时间间隔划分为不同的日期并确定isdaydiff标志。这从初始间隔开始，不断递归添加新行，直到覆盖整个间隔。在使用UNION ALL运算符组合每个递归阶段的结果之后，最终结果被输入到tbl_bifurcation表中。tbl_bifurcation表从查询中生成所需的输出以及准确的isdaydiff值。
希望它能起作用：）

赞(0）回复(0）举报 2023-10-18

我来回答

postgresql 插入具有预期逻辑的查询以将表分叉

2条答案

相关问题

热门标签

最新问答