我需要关于重写此查询的指导
我需要关于重写此查询的指导
提问于 2019-05-20 16:10:15
我们有这个查询，我们运行它来生成日历周数据，这个查询两次命中同一个视图。并且可能由于缺少join ON子句而创建笛卡尔乘积。
有没有办法以最佳方式重写这个查询。
SELECT cal_date,
regexp_replace(cal_date, '-', '') AS PC_cal_date,
year_num*100+week_num AS year_week_num,
CASE
WHEN year_num*100+pd_num IN (min_year_pd_num, max_year_pd_num) THEN 'A'
ELSE 'B'
END AS yr_pd_ind,
year_num*100+pd_num AS yr_pd_num,
dense_rank() OVER (ORDER BY year_num*100+week_num DESC) AS wk_index,
dense_rank() OVER (ORDER BY year_num*100+pd_num DESC) AS pd_index
FROM mstr_v.local_cal_date t1,
(SELECT max(year_num*100+pd_num) max_year_pd_num,
min(year_num*100+pd_num) min_year_pd_num
FROM mstr_v.local_cal_date
WHERE cal_date IN (date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+105*7+1)),
date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)))) ) t2
WHERE cal_date BETWEEN date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+105*7))
AND date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+1))回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-20 17:13:17
如果移动当前位于t2 into case语句中的where子句，并使用over()计算最小和最大值，则无需第二次表扫描(在同一子查询中)即可计算出在t2子查询中计算出的列：
SELECT cal_date,
regexp_replace(cal_date, '-', '') AS PC_cal_date,
year_num*100+week_num AS year_week_num,
CASE
WHEN year_num*100+pd_num IN (min_year_pd_num, max_year_pd_num) THEN 'A'
ELSE 'B'
END AS yr_pd_ind,
year_num*100+pd_num AS yr_pd_num,
dense_rank() OVER (ORDER BY year_num*100+week_num DESC) AS wk_index,
dense_rank() OVER (ORDER BY year_num*100+pd_num DESC) AS pd_index
FROM (select t1.*,
max(case when cal_date IN (date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+105*7+1)),
date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int))))
then year_num*100+pd_num end) over() as max_year_pd_num,
min(case when cal_date IN (date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+105*7+1)),
date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int))))
then year_num*100+pd_num end) over() as min_year_pd_num
from mstr_v.local_cal_date t1
)t1
WHERE cal_date BETWEEN date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+105*7))
AND date(date_sub(CURRENT_DATE, cast(date_format(CURRENT_DATE, 'u') AS int)+1))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56216710
复制相关文章
相似问题