很难构建聚合SQL查询

发布于 2025-01-31 18:55:40 字数 2726 浏览 0 评论 0原文

我是SQL的新手，对基本内容有很好的了解，但我一直坚持我的要求。

我的请求使我获得下表（右端右侧的最后一列除外）：

团队	变量	日期	column_i_i_to_to_add	AA AA
a	aa aa	2022/05/01	100	0
A	AA	2022/06/01	25	0
A	AA	2022/07/01/01	580	0
A	AD	2022/08/01	50	605
B	AA	2021/05/01	75	0
B	AA	2021/06/01	110 110	0
B	AA	2021/07/01	514	0
B	AD	2021/08/08/01	213	624

我不能什么我不能什么转过头，是如何通过将同一团队的AA变量的值求和的最后一列编码填充AD变量的行的方法，但仅在AD变量日期之前的两个月内。

到目前为止，这是我到目前为止的脚本，让我获得了前四列：

SELECT
 
    team.Team, 

    Var.Variable, 

    TO_DATE(Var.Year||'-'||LPAD(Var.Month,2,'00')||'-'||'01','YYYY-MM-DD')AS Date , 

    Var.value 

FROM table1 as Var

join table2 as team

on Var.code=team.code
---This last join with table3 is only there to add other columns that are not relevant to this problem.
---join table3 as detail_var on Var.variable=detail_var.code_var

原文

I am new at SQL and have a pretty good knowledge of basic stuff but I am stuck with my request.

My request gets me te following table (except for the last column on the right end side):

Team	Variable	Date	Value	Column_I_need_to_add
A	aa	2022/05/01	100	0
A	aa	2022/06/01	25	0
A	aa	2022/07/01	580	0
A	ad	2022/08/01	50	605
B	aa	2021/05/01	75	0
B	aa	2021/06/01	110	0
B	aa	2021/07/01	514	0
B	ad	2021/08/01	213	624

What I cannot turn my head around, is how to code for the last column that fills rows for the ad variable by summing values of the aa variables of the same team but only for the two months prior to the date of the ad variable.

Here is the script I have so far, that gets me the first four columns:

SELECT
 
    team.Team, 

    Var.Variable, 

    TO_DATE(Var.Year||'-'||LPAD(Var.Month,2,'00')||'-'||'01','YYYY-MM-DD')AS Date , 

    Var.value 

FROM table1 as Var

join table2 as team

on Var.code=team.code
---This last join with table3 is only there to add other columns that are not relevant to this problem.
---join table3 as detail_var on Var.variable=detail_var.code_var

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陪我终i 2025-02-07 18:55:40

正如进一步阅读所能理解的那样，我对以前的答案不满意。因此，必须进行一些进一步的磨削，这就是我想到的（现在是Postgres 13）。

它更干净，并以收藏家的方式完成工作。我还添加了a fiddle link。如果您想查看上一个答案，请查看编辑版本。

SELECT
  team.Team
  ,var.Variable
  ,var.Date
  ,var.value
  ,CASE
    WHEN var.Variable='ad' THEN 
      (SELECT sum(value) FROM table1
       WHERE 
          (TO_DATE(Year||'-'||LPAD(Month::varchar(2),2,'0')||'-'||'01','YYYY-MM-DD')
              BETWEEN (var.Date - INTERVAL '2 month') AND var.Date)
          AND Variable = 'aa'
          AND code = var.code)
    ELSE null
  END as past2monthsValue
FROM (
  -- this sub query to change Year & Month to Date Type Value
  -- this Date Type Value (Date) will be used to compare dates
  -- (var.Date) in the above sub-query
  SELECT
    code,
    Variable,
    TO_DATE(Year||'-'||LPAD(Month::varchar(2),2,'0')||'-'||'01','YYYY-MM-DD') AS Date,
    value  
  FROM table1    
) var

JOIN table2 AS team ON var.code=team.code

I was not content with the previous answer, with OUTER APPLY, as understood from further reading. So had to do a bit of further grinding and this is what I came up with (Now for Postgres 13).

It is cleaner and does the job in a conciser fashion. I've also added a FIDDLE LINK. If you want to see the previous answer please look at the edit versions.

SELECT
  team.Team
  ,var.Variable
  ,var.Date
  ,var.value
  ,CASE
    WHEN var.Variable='ad' THEN 
      (SELECT sum(value) FROM table1
       WHERE 
          (TO_DATE(Year||'-'||LPAD(Month::varchar(2),2,'0')||'-'||'01','YYYY-MM-DD')
              BETWEEN (var.Date - INTERVAL '2 month') AND var.Date)
          AND Variable = 'aa'
          AND code = var.code)
    ELSE null
  END as past2monthsValue
FROM (
  -- this sub query to change Year & Month to Date Type Value
  -- this Date Type Value (Date) will be used to compare dates
  -- (var.Date) in the above sub-query
  SELECT
    code,
    Variable,
    TO_DATE(Year||'-'||LPAD(Month::varchar(2),2,'0')||'-'||'01','YYYY-MM-DD') AS Date,
    value  
  FROM table1    
) var

JOIN table2 AS team ON var.code=team.code

回复收藏 0 原文

~没有更多了~