Сделайте повторяющиеся выражения в выбранном запросе более эффективным ⇐ MySql

[Anonymous]

Я пытаюсь сделать проект анализа временных рядов, чтобы узнать больше и проверить свои собственные навыки. Для этого анализа временных рядов я хочу показать средние значения движущихся и процентные различия в период, ежедневные, ежемесячные и ежегодно. Я считаю, что проблема эффективности связана с тем, что ссылается на таблицу слишком много раз в избранном запросе, и это заставляет его навсегда компилировать. Наряду с большой функцией случая, которая повторяется три раза. Я не самый знаком с SQL, но есть ли я какой -нибудь способ использовать рекурсию или выполнить функцию, чтобы призывать к повторяющемуся выражению? Любая помощь, чтобы сделать это более эффективным, очень ценится!
Я также использую MySQL для этого проекта.WITH debt_lag AS(
SELECT *, LAG(`Debt Held by the Public`, 7) OVER (ORDER BY `Record Date`) AS week_b4_dhpb,
LAG(`Debt Held by the Public`, 7) OVER (ORDER BY `Record Date`) AS month_b4_dhpb,
LAG(`Debt Held by the Public`, 365) OVER (ORDER BY `Record Date`) AS year_b4_dhpb,
LAG(`Intragovernmental Holdings`, 7) OVER (ORDER BY `Record Date`) AS week_b4_ig,
LAG(`Intragovernmental Holdings`, 7) OVER (ORDER BY `Record Date`) AS month_b4_ig,
LAG(`Intragovernmental Holdings`, 365) OVER (ORDER BY `Record Date`) AS year_b4_ig,
LAG(`Total Public Debt Outstanding`, 7) OVER (ORDER BY `Record Date`) AS week_b4_tdpo,
LAG(`Total Public Debt Outstanding`, 7) OVER (ORDER BY `Record Date`) AS month_b4_tdpo,
LAG(`Total Public Debt Outstanding`, 365) OVER (ORDER BY `Record Date`) AS year_b4_tdpo

FROM DebtPenny_19930401_20250623
)

SELECT `Record Date`, (((`Debt Held by the Public` - week_b4_dhpb) / week_b4_dhpb )* 100) AS DHPB_percent_change_weekly,
ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),3) AS DHPB_WEEKLY_MOVING_AVERAGE,
(((`Debt Held by the Public` - month_b4_dhpb) / month_b4_dhpb )* 100) AS DHPB_percent_change_monthly,
CASE
WHEN SUBSTRING(`Record Date`,6,2) = '01' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '02' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '03' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '04' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '05' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '06' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '07' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '08' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '09' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '10' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '11' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '12' THEN ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)

END AS DHPB_MONTHLY_MOVING_AVERAGE,
(((`Debt Held by the Public` - year_b4_dhpb) / year_b4_dhpb )* 100) AS DHPB_percent_change_annual,
ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 364 PRECEDING AND CURRENT ROW),3) AS DHPB_ANNUAL_MOVING_AVERAGE,

(((`Intragovernmental Holdings` - week_b4_ig) / week_b4_ig )* 100) AS IG_percent_change_weekly,
ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),3) AS IG_WEEKLY_MOVING_AVERAGE,
(((`Intragovernmental Holdings` - month_b4_ig) / month_b4_ig)* 100) AS IG_percent_change_monthly,
CASE

WHEN SUBSTRING(`Record Date`,6,2) = '01' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '02' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '03' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '04' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '05' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '06' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '07' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '08' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '09' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '10' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '11' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '12' THEN ROUND(AVG(`Intragovernmental Holdings`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)

END AS IG_MONTHLY_MOVING_AVERAGE,

(((`Debt Held by the Public` - year_b4_ig) / year_b4_ig )* 100) AS IG_percent_change_annual,
ROUND(AVG(`Debt Held by the Public`) OVER (ORDER BY `Record Date` ROWS BETWEEN 364 PRECEDING AND CURRENT ROW),3) AS IG_ANNUAL_MOVING_AVERAGE,

(((`Total Public Debt Outstanding` - week_b4_tdpo) / week_b4_tdpo )* 100) AS TDPO_percent_change_weekly,
ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),3) AS TDPO_WEEKLY_MOVING_AVERAGE,
(((`Total Public Debt Outstanding` - month_b4_ig) / month_b4_ig)* 100) AS TDPO_percent_change_monthly,
CASE

WHEN SUBSTRING(`Record Date`,6,2) = '01' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '02' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '03' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '04' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '05' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '06' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '07' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '08' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '09' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '10' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '11' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 29 PRECEDING AND CURRENT ROW),3)
WHEN SUBSTRING(`Record Date`,6,2) = '12' THEN ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 30 PRECEDING AND CURRENT ROW),3)

END AS TDPO_MONTHLY_MOVING_AVERAGE,

(((`Total Public Debt Outstanding` - year_b4_tdpo) / year_b4_tdpo )* 100) AS TDPO_percent_change_annual,
ROUND(AVG(`Total Public Debt Outstanding`) OVER (ORDER BY `Record Date` ROWS BETWEEN 364 PRECEDING AND CURRENT ROW),3) AS TDPO_ANNUAL_MOVING_AVERAGE

FROM debt_lag;


Подробнее здесь: https://stackoverflow.com/questions/796 ... -efficient