Date
計算日期之間的天數並找到最大的差距
我需要從一個日曆月中找到我們數據庫中的所有成員，這些成員在發送數據之間/或整個月中沒有超過 10 天的間隔，即 2018 年 4 月的每個人。該表的設置是這樣的（儘管要大得多） ：
+-----------+------------+ | member_id | data_date | +-----------+------------+ | 1 | 2018-04-10 | | 5 | 2018-04-16 | | 1 | 2018-04-11 | | 2 | 2018-04-12 | | 3 | 2018-04-13 | | 4 | 2018-04-12 | | 5 | 2018-04-15 | | 3 | 2018-04-19 | | 2 | 2018-04-17 | | 1 | 2018-04-18 | | 5 | 2018-04-10 | | 2 | 2018-04-18 | | 1 | 2018-04-08 | | 2 | 2018-04-03 | | 3 | 2018-04-02 | | 4 | 2018-04-14 | | 5 | 2018-04-15 | | 3 | 2018-04-16 | | 2 | 2018-04-19 | | 1 | 2018-04-14 | +-----------+------------+
(member_id,data_date)被定義UNIQUE。每個 data_date 代表數據發送的一天。每個 member_id 都有重複的 data_dates。我正在執行 PostgreSQL 8.2.15。是綠梅。每個月的每個 member_id 最多有 30 個 data_dates，我無法弄清楚如何在不為每個成員在整個月內發送數據的情況下找到最大的差距。
以下是一些測試數據的範例：
create temp table tempdata ( member_id integer NOT NULL, data_date date ); INSERT INTO tempdata(member_id, data_date) VALUES (1, '2017-04-01') , (1, '2017-04-02') , (1, '2017-04-03') , (1, '2017-04-04') , (1, '2017-04-05') , (1, '2017-04-06') , (1, '2017-04-07') , (1, '2017-04-08') , (1, '2017-04-09') , (1, '2017-04-10') , (1, '2017-04-11') , (1, '2017-04-12') , (1, '2017-04-13') , (1, '2017-04-14') , (1, '2017-04-15') , (1, '2017-04-16') , (1, '2017-04-17') , (1, '2017-04-18') , (1, '2017-04-19') , (1, '2017-04-20') , (1, '2017-04-21') , (1, '2017-04-22') , (1, '2017-04-23') , (1, '2017-04-24') , (1, '2017-04-25') , (1, '2017-04-26') , (1, '2017-04-27') , (1, '2017-04-28') , (1, '2017-04-29') , (1, '2017-04-30') , (2, '2017-04-09') , (2, '2017-04-10') , (2, '2017-04-11') , (2, '2017-04-12') , (3, '2017-04-01') , (3, '2017-04-02') , (3, '2017-04-03') , (3, '2017-04-04') , (3, '2017-04-05') , (3, '2017-04-06') , (3, '2017-04-07') , (3, '2017-04-08') , (3, '2017-04-09') , (3, '2017-04-10') , (3, '2017-04-11') , (3, '2017-04-12') , (3, '2017-04-13') , (3, '2017-04-14') , (3, '2017-04-15') , (3, '2017-04-16') , (3, '2017-04-17') , (3, '2017-04-18') , (3, '2017-04-19') , (3, '2017-04-20') , (3, '2017-04-21') , (3, '2017-04-22') , (3, '2017-04-23') , (3, '2017-04-24') , (3, '2017-04-25') , (3, '2017-04-26') , (3, '2017-04-27') , (3, '2017-04-28') , (3, '2017-04-29') , (3, '2017-04-30') , (4, '2017-04-01') , (4, '2017-04-02') , (4, '2017-04-03') , (4, '2017-04-04') , (4, '2017-04-05') , (4, '2017-04-06') , (4, '2017-04-07') , (4, '2017-04-08') , (4, '2017-04-09') , (4, '2017-04-10') , (4, '2017-04-11') , (4, '2017-04-12') , (4, '2017-04-13') , (4, '2017-04-14') , (4, '2017-04-15') , (4, '2017-04-16') , (4, '2017-04-17') , (4, '2017-04-18') , (4, '2017-04-19') , (4, '2017-04-20') , (4, '2017-04-21') , (4, '2017-04-22') , (5, '2017-04-01') , (5, '2017-04-02') , (5, '2017-04-03') , (5, '2017-04-04') , (5, '2017-04-05') , (5, '2017-04-06') , (5, '2017-04-07') , (5, '2017-04-08') , (5, '2017-04-09') , (5, '2017-04-10') , (5, '2017-04-11') , (5, '2017-04-12') , (5, '2017-04-13') , (5, '2017-04-14') , (5, '2017-04-15') , (5, '2017-04-16') , (5, '2017-04-17') , (5, '2017-04-18') , (5, '2017-04-22') , (5, '2017-04-23') , (5, '2017-04-24') , (5, '2017-04-25') , (5, '2017-04-26') , (5, '2017-04-27') , (5, '2017-04-29') , (5, '2017-04-30') , (6, '2017-04-01') , (6, '2017-04-02') , (6, '2017-04-03') , (6, '2017-04-04') , (6, '2017-04-05') , (6, '2017-04-06') , (6, '2017-04-07') , (6, '2017-04-08') , (6, '2017-04-09') , (6, '2017-04-10') , (7, '2017-04-01') , (7, '2017-04-04') , (7, '2017-04-05') , (7, '2017-04-06') , (7, '2017-04-07') , (7, '2017-04-08') , (7, '2017-04-09') , (7, '2017-04-11') , (7, '2017-04-12') , (7, '2017-04-13') , (7, '2017-04-14') , (7, '2017-04-15') , (7, '2017-04-16') , (7, '2017-04-17') , (7, '2017-04-18') , (7, '2017-04-19') , (7, '2017-04-21') , (7, '2017-04-22') , (7, '2017-04-26') , (7, '2017-04-27') , (7, '2017-04-28') , (7, '2017-04-30') , (8, '2017-04-02') , (8, '2017-04-03') , (8, '2017-04-04') , (8, '2017-04-05') ;
更新我為任何感興趣的人找到的答案：
drop table if exists tempdata; create temp table tempdata as select distinct member_id from ( select member_id , max(data_date) over (partition by member_id order by data_date) start_range , lead(data_date) over (partition by member_id order by data_date) end_range from tempmembers ) as c where c.end_range-c.start_range > interval'9 days' ;我能夠使用它，然後刪除我從初始表中找到的 ID。