递归PostgreSQL动态平均

本·德鲁里（Ben Drury）

我有无法解决的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中这可能吗，我该怎么做？

cske

想法是在开始日期和结束日期之间生成一系列星期，从就业中选择开始和结束星期，然后计算每个星期。我没有针对绑定案例进行过测试，但是OP coud的开头是

WITH startDate(d) as (VALUES ('2010-01-01'::DATE))
   , endDate(d) as (VALUES ('2016-06-06'::DATE))
   , weeks as (select to_char(startDate.d+s.a,'YYYY-WW') as w
               from startDate,endDate,generate_series(0,(endDate.d - startDate.d),7) as s(a))
   , emp as (select name,to_char(sd,'YYYY-WW') as sw
                       , to_char(coalesce(ed,endDate.d),'YYYY-WW') as ew 
             from startDate,endDate,public.so where sd > startDate.d )
SELECT 
   w.w
  ,(select ARRAY_AGG(name) from emp Where w.w BETWEEN sw AND ew ) as emps
  ,(select count(name) from emp Where w.w BETWEEN sw AND ew ) as empCount
FROM weeks w

测试设置

create table public.so (
  name TEXT
  ,sd DATE
  ,ed DATE
);

INSERT INTO public.so (name,sd,ed) VALUES 
   ('a','2011-05-23','2016-04-16')
  ,('b','2012-09-17','2016-03-16')
  ,('c','2009-12-12',null)
  ,('d','2015-03-30','2016-03-01')
  ,('e','2012-06-27',null)
  ,('f','2014-02-21','2016-04-13')
  ,('g','2016-03-27',null)
  ,('h','2010-06-15','2016-05-16')
;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。