时间戳

首页 > 解决方案 > 如何在postgresql的以下条件下从三个表中检索信息

postgresql - 如何在postgresql的以下条件下从三个表中检索信息

问题描述

我有三张桌子。

表格1：

T2_ID   ver          date                         boolean
---------------------------------------------------------
  1  | X-20-50 | 2019-01-01 16:20:51.722336+00 | TRUE 
  2  | X-50-30 | 2019-02-26 16:20:51.722336+00 | TRUE
  3  | X-20-32 | 2019-03-20 16:20:51.722336+00 | FALSE
  1  | X-20-50 | 2019-01-09 16:20:51.722336+00 | FALSE
  2  | X-20-50 | 2019-12-02 16:20:51.722336+00 | TRUE
  3  | X-20-50 | 2019-01-24 16:20:51.722336+00 | TRUE

表_2：

id | type | scheduler
--------------------------------------------------
1  | ABC  | w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12
2  | PQR  | w5,w9
3  | TRC  | w1,w4,w8

表3

    start_date_of_ver  |    end_date_of_ver     | ver_name
-----------------------------------------------------------
2019-01-01 00:00:00+00 | 2019-04-01 00:00:00+00 | X-20-50
2019-02-25 00:00:00+00 | 2019-05-26 00:00:00+00 | X-50-30
2019-03-15 00:00:00+00 | 2019-06-06 00:00:00+00 | X-20-32

表 4 应满足以下条件。

注：表 1，条目每天生成。

所需表：只有ver_name作为输入并计算下表。

当 table_1 没有任何条目时， table_4 应该如下所示

表_4：X-20-50

id_of_table_2 | week_1 | week_2 | week_3 | week_4 | week_5 | week_6 | week_7 | week_8 | week_9 | week_10 | week_11 | week_12 |
------------------------------------------------------------------------------------------------------------------------------
     ABC      |    w1  |    w2  |    w3  |    w4  |    w5  |    w6  |    w7  |    w8  |    w9  |    w10  |    w11  |    w12  |
     PQR      |        |        |        |        |    w5  |        |        |        |    w9  |         |         |         |
     TRC      |    w1  |        |        |    w4  |        |        |        |    w8  |        |         |         |         |

当 table_1 有条目时， table_4 应该如下所示

X-20-50

id_of_table_2 | week_1 | week_2 | week_3 | week_4 | week_5 | week_6 | week_7 | week_8 | week_9 | week_10 | week_11 | week_12 |
------------------------------------------------------------------------------------------------------------------------------
     ABC      |  Done  |  Done  |    w3  |    w4  |    w5  |    w6  |    w7  |    w8  |    w9  |    w10  |    w11  |    w12  |
     PQR      |        |        |        |        |    w5  |        |        |        |    w9  |         |         |         |
     TRC      |  Done  |        |        |    w4  |        |        |        |    w8  |        |         |         |         |

标签： postgresql

解决方案

您的要求需要稍加改进。您指定检索每周数据但未能定义您的一周。它从哪一天开始？所有周都是 7 天吗？当 12 月 31 日在星期二是同一周的 1 月 3 日星期五时会发生什么（请参阅当年日历）。然后是用户输入及其代表的问题。它是期望的开始日期和星期是该日期和接下来的 6 天还是每周内的任何日期？
下面假设一个 ISO 8601 定义（谷歌它 - 很多东西）。每周从星期一开始，所有周均为 7 天。（因此，包含 2019 年 12 月 31 日的一周也包括 2020 年 1 月 3 日）。该例程提取用户输入的 ISO 年和 ISO 周日期。

--setup
 create table  weekly_something( c1 text, c2 text, date1 timestamptz, someem boolean);   
 insert into weekly_something( c1, c2, date1, someem )  
      values ('ABC','AB-20-50','2019-11-25 16:20:51.722336+00',TRUE) 
           , ('PQR','AB-50-30','2019-11-26 16:20:51.722336+00',TRUE)
           , ('TRC','CD-20-32','2019-11-27 16:20:51.722336+00',FALSE)
           , ('ABC','AB-20-50','2019-12-02 16:20:51.722336+00',FALSE)
           , ('ABC','AB-20-50','2019-12-02 16:20:51.722336+00',TRUE) 
           , ('JFF','yy-45-89','2019-12-31 16:20:51.722336+00',TRUE)
           , ('JFF','yy-89-30','2020-01-03 16:20:51.722336+00',TRUE) ;          
            -- JFF Just For Fun

-- SQL Function
create  function week_of(week_date date)
returns setof weekly_something
language sql stable strict
as $$
    select *
      from weekly_something 
     where (extract('isoyear' from week_date), extract('week' from week_date)) =
           (extract('isoyear' from date1), extract('week' from date1));
$$;

-- test
select * from week_of('2019-11-26');
select * from week_of('2019-12-30');

推荐阅读