英文:
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:
-
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.
-
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 '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 |
See this demonstrated here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论