在SQL中,”RECURSIVE”用于处理更复杂的查询(与日期间隔相关)。

huangapple go评论69阅读模式
英文:

RECURSIVE in SQL for more complicated queries (date interval related)

问题

The code you provided seems to be a SQL query attempting to perform a recursive calculation to sum sales prices for specific date ranges. It appears that you have concerns about the behavior of your query. Based on the information provided, here's a brief explanation of the issues you're facing:

  1. Looping Over the Same Days:

    • The loop you've set up in your recursive query is designed to iterate over date ranges in the past, specifically three months at a time.
    • The reason it appears to loop over the same days for each SDATE1 is likely due to the WHERE conditions in your recursive CteRecursive section.
    • The WHERE conditions restrict Datereceived to be within specific date intervals relative to SDATE2 (the initial date).
    • Since your initial SDATE2 is the same for all rows, this can cause the same days to be included in multiple iterations because the date ranges overlap.
  2. Iterating Over Each Day:

    • The reason your query is iterating over each day instead of summing the whole quarter is related to how you've structured your recursive query.
    • In the CteRecursive section, you're selecting individual rows from your data source (adjudicationresult) based on date intervals. This will give you individual rows for each day within those intervals, leading to the behavior you observed.
    • If you want to sum the sales prices for the whole quarter, you should adjust your query to calculate the sum for each quarter rather than individual days within the quarter.

To resolve these issues, you may need to revise your SQL query logic to calculate quarterly sums correctly and ensure that the recursive loop is working as intended to accumulate the quarterly totals over the specified time period. This might involve modifying the WHERE conditions and how you calculate the quarterly sums within the CteRecursive section of your query.

英文:

Goal: to create a recursive query that gets the last 3 months of a table and sums them for every 3 months back 3 years.

I have a big table but it has sales price as a column and sales date as a column. I want to sum the sales price for all dates in-between now() and the last 3 months then again for months 3 to 6 and again for months 6 to 9 and on all the way back 3 years.

This is the code I have written so far it seems that SDATE1 is iterating the right way but my totpaid is not

WITH RECURSIVE PARAMS AS (SELECT '07/01/2023' SDATE, '05/01/23' EDATE), --(this is going to be put in to SSRS so I am hard coding SDATE to with) 

 CteRecursive(SDATE1, totpaid, datereceived) AS (
    Select *
    From (
    select (cast((SELECT SDATE FROM PARAMS) as TIMESTAMP)) SDATE2, sum(totpaid),Datereceived
    From adjudicationresult ar
      where Locations = 'GRE'
      GROUP BY Datereceived
     ) cte
      Where Datereceived <= (cast((SDATE2 ) as date)- (INTERVAL '1 Month' ))
        and Datereceived >= (cast((SDATE2 ) as date)- (INTERVAL '3 Month' ))
--**This is where I think I went wrong trying to get SDATE2 to update

  UNION ALL
    SELECT (SDATE1 - INTERVAL '3 MONTH') SDate3 ,totpaid, Datereceived
    FROM t
    WHERE SDATE1 > (cast((SELECT SDATE FROM PARAMS) as date)- (INTERVAL '12 Month' ))
    GROUP BY t.SDATE1, t.totpaid , t.Datereceived
)
SELECT Sdate1, (totpaid), datereceived
 FROM t
ORDER BY TotPaid;

my results are

SDATE1 TOTPAID daterecived
2023-01-01T00:00:00 -49.74 2023-05-21
2023-04-01T00:00:00 -49.74 2023-05-21
2022-07-01T00:00:00 -49.74 2023-05-21
2022-10-01T00:00:00 -49.74 2023-05-21
2023-07-01T00:00:00 -49.74 2023-05-21
2023-01-01T00:00:00 22.08 2023-05-07
2023-04-01T00:00:00 22.08 2023-05-07
2022-10-01T00:00:00 22.08 2023-05-07

Or if sorted by sdate1, daterecived

SDATE1 TOTPAID daterecived
2022-07-01T00:00:00 1587.84 2023-04-01
2022-07-01T00:00:00 285.83 2023-04-02
2022-07-01T00:00:00 174746.59 2023-04-03
2022-07-01T00:00:00 63322.25 2023-04-04
2022-07-01T00:00:00 38465.58 2023-04-05
2022-07-01T00:00:00 30848.58 2023-04-06
2022-07-01T00:00:00 527.58 2023-04-07
2022-07-01T00:00:00 67957.30 2023-04-08
2022-07-01T00:00:00 83921.99 2023-04-10
2022-07-01T00:00:00 39771.13 2023-04-11
2022-07-01T00:00:00 64172.55 2023-04-12

My Questions are why did it loop for the same days for each sdate1 (ie 2023-01-01:2023-04-01 ect)
and why did iterate over each day instead of summing the whole quarter

答案1

得分: 0

更传统且可能性能更好的方法是生成一系列日期范围,然后通过这些范围进行聚合。例如:

SELECT
    r AS start_date,
    r + INTERVAL '3 months' AS end_date,
    sum(totpaid)
FROM
    generate_series(
        CURRENT_DATE - INTERVAL '3 years',
        CURRENT_DATE - INTERVAL '3 months',
        INTERVAL '3 months'
    ) AS r
LEFT JOIN adjudicationresult AS s ON s.Datereceived >= r
                                     AND s.Datereceived < r + INTERVAL '3 months'
GROUP BY
    r,
    r + INTERVAL '3 months'
start_date end_date sum
2020-07-18 00:00:00 2020-10-18 00:00:00 300.03
2022-04-18 00:00:00 2022-07-18 00:00:00 null
2020-10-18 00:00:00 2021-01-18 00:00:00 300.03
2022-10-18 00:00:00 2023-01-18 00:00:00 null
2021-07-18 00:00:00 2021-10-18 00:00:00 300.03
2021-10-18 00:00:00 2022-01-18 00:00:00 400.04
2021-04-18 00:00:00 2021-07-18 00:00:00 300.03
2021-01-18 00:00:00 2021-04-18 00:00:00 300.03
2022-01-18 00:00:00 2022-04-18 00:00:00 null
2022-07-18 00:00:00 2022-10-18 00:00:00 null
2023-01-18 00:00:00 2023-04-18 00:00:00 null
2023-04-18 00:00:00 2023-07-18 00:00:00 null

可以在这里查看演示。

英文:

A more conventional, and likely better performing, approach is to generate a series of date ranges, then aggregate via those ranges. e.g:

SELECT
      r AS start_date
    , r + INTERVAL &#39;3 months&#39; AS end_date
    , sum(totpaid)
FROM
    generate_series(
        CURRENT_DATE - INTERVAL &#39;3 years&#39;,
        CURRENT_DATE - INTERVAL &#39;3 months&#39;,
        INTERVAL &#39;3 months&#39;
    ) AS r
LEFT JOIN adjudicationresult AS s ON s.Datereceived &gt;= r
                                 AND s.Datereceived &lt; r + INTERVAL &#39;3 months&#39;
GROUP BY
      r
    , r + INTERVAL &#39;3 months&#39;
start_date end_date sum
2020-07-18 00:00:00 2020-10-18 00:00:00 300.03
2022-04-18 00:00:00 2022-07-18 00:00:00 null
2020-10-18 00:00:00 2021-01-18 00:00:00 300.03
2022-10-18 00:00:00 2023-01-18 00:00:00 null
2021-07-18 00:00:00 2021-10-18 00:00:00 300.03
2021-10-18 00:00:00 2022-01-18 00:00:00 400.04
2021-04-18 00:00:00 2021-07-18 00:00:00 300.03
2021-01-18 00:00:00 2021-04-18 00:00:00 300.03
2022-01-18 00:00:00 2022-04-18 00:00:00 null
2022-07-18 00:00:00 2022-10-18 00:00:00 null
2023-01-18 00:00:00 2023-04-18 00:00:00 null
2023-04-18 00:00:00 2023-07-18 00:00:00 null

See this demonstrated here

huangapple
  • 本文由 发表于 2023年7月18日 07:59:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/76708767.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定