postgresql - 递归 PostgreSQL 动态平均

Question

我有无法解决的 PostgreSQL 动态平均问题:

我有个人的就业开始和结束日期数据如下:

"parentid"                               "Name"                "startdate"     "enddate"

"01e7de72-843d-4aa5-b3ae-2e2887d1b342"   "Isabelle Smith"     "2011-05-23"   "2016-04-16"
"027ee658-8c4d-4910-b93e-62c0900f2147"  "Emelie Blogs"        "2012-09-17"   "2016-03-16"
"02cbb478-adf3-4a8b-a5aa-ae9f03943ce4"  "Joshauh Jow"         "2015-04-04"   NULL
"0328f382-2845-4623-a940-ab68af5d11cc"  "VICTORIA Fred"       "2015-05-11"   NULL
"03823a20-51bc-4ae5-ab73-79056355ea36"  "Elin Tree"           "2014-03-24"   NULL   
"03878ef8-1c3a-4310-b3d5-7b8d18634707"  "Michaela Apple"      "2011-07-08"   NULL   
"03c36926-395b-4e3c-9f77-c6214ce763a2"  "Immad Cheese"        "2012-05-15"   NULL   
"0436824c-29a6-4140-ba4a-d0f56facd8fc"  "Burak Teal"          "2009-06-22"   NULL   
"04d7a07a-0ad4-4091-98d2-a7ff35798b6f"  "Roberto Purple"      "2015-03-30"   "2016-03-01"
"04f32c2f-887f-4e03-be67-bc023aa3a7c2"  "Iftikar Orange"      "2012-06-27"   NULL   
"055b690a-153a-49c8-8ac0-112681f79551"  "Josef Red"           "2014-02-21"   "2016-04-13"
"055be2f6-baec-4626-b876-7ff16dc95464"  "Harry Green"         "2016-03-27"   NULL   
"05a570b0-ec76-49d9-a742-5bf08f215fec"  "Sofie Blue"          "2010-06-15"   "2016-05-16"
"05c92e7a-efde-44f0-a57c-298cbe129259"  "BANARAS Yellow"      "2015-06-22"   NULL   
"05fe0113-9bda-407b-bd72-5bf2a9deae15"  "Bengt  Drury"        "2015-03-30"   "2016-06-16"
"063c454f-2e97-48a8-96fc-9e84d29f5d96"  "Son That"            "2016-03-27"   NULL   
"07b76b47-8086-4df6-a3da-50dcfcd2de89"  "Sam This"            "2015-03-21"   "2016-05-24"
"082771ee-2f02-4623-abc2-696447f9f791"  "Felix This"          "2014-11-24"   "2016-05-31"
"08e39639-176b-4f44-ae75-1025219730c6"  "ROBIN That"          "2015-10-26"   NULL   
"09ab8491-9d9a-4091-b448-8315e3b5d3f0"  "Kaziah This"         "2016-05-14"   NULL   
"0a74dd0c-e1ee-4b32-a893-c486f7402363"  "Luke Him"            "2015-12-16"   NULL   
"0b098799-7d92-47df-9778-b48edf948af9"  "MARIA Her"           "2015-05-11"   NULL   
"0b480b25-8d2b-441b-8039-48b4e9188769"  "That Adebayor"       "2015-04-09"   NULL   
"0b86b44e-f3e0-4ddf-8e72-e0d7f9470279"  "This Ålund"          "2012-02-07"   "2016-06-05"
"0c3e13d0-f602-41da-b10c-f70072605e63"  "First Ekmark"        "2013-02-08"   NULL   
"0d2367f4-a6b4-4381-b7dc-3e0c9063285f"  "Anna Check"          "2015-03-13"   NULL   
"0e31731b-0384-43ef-adeb-503ad5a137f9"  "Assign Test1"        "2015-05-22"   NULL   
"0e3f8b57-cba2-4240-abd4-d157832ef421"  "Ramises Person       "2016-10-11"   NULL   
"0f6af1c8-7672-4f0b-912c-91675cf52845"  "Lars Surname"        "2016-03-28"   NULL   

对于此报告，用户将输入两个日期 startOfPeriod 和 endOfPeriod

我需要一个 SQL 语句，对于那些动态日期，它可以每周输出该期间每周雇用的人数。(从 startOfPeriod 日期起每 7 天构成一周)

这在 PostgreSQL 中可行吗？我该怎么做？

Answer 1

使用类型 daterange和 overlap operator && .

WITH 中的第一个查询定义了时间段，第二个查询生成一系列星期:

with period(start_of_period, end_of_period) as (
    values ('2012-01-20'::date, '2012-02-15'::date)
),
weeks as (
    select daterange(d::date, d::date+ 7) a_week
    from period, 
    lateral generate_series (start_of_period, end_of_period, '7d'::interval) d
)
select lower(a_week) start_of_week, count(*)
from weeks
left join a_table
on daterange(startdate, enddate) && a_week
group by 1
order by 1;

 start_of_week | count 
---------------+-------
 2012-01-20    |     4
 2012-01-27    |     4
 2012-02-03    |     5
 2012-02-10    |     5
(4 rows)