2022-06-23
求问这两个sql有区别吗
最近在学习窗口函数，这是我自己改写的sql：select calendar_year,week,sale,sum(sale) from sales_fact where country in ('Australia') and product='Xtend Memory' Group by calendar_year,week,sale order by CALENDAR_YEAR,week,sale /复制
CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1998 1 58.15 58.15 1998 2 29.39 29.39 1998 3 29.49 29.49 1998 4 29.49 29.49 1998 5 29.8 29.8 1998 6 58.78 58.78 1998 9 58.78 58.78 1998 10 117.76 117.76 1998 12 59.6 59.6 1998 14 58.78 58.78 1998 15 58.78 58.78 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1998 17 58.78 58.78 1998 18 117.56 117.56 1998 19 58.98 58.98 1998 21 59.6 59.6 1998 23 117.56 117.56 1998 26 117.56 117.56 1998 27 57.52 57.52 1998 28 57.72 57.72 1998 29 57.72 57.72 1998 34 115.44 115.44 1998 35 57.52 57.52 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1998 38 115.84 115.84 1998 39 115.84 115.84 1998 40 57.52 57.52 1998 41 58.32 58.32 1998 42 115.84 115.84 1998 43 57.52 57.52 1998 44 57.52 57.52 1998 45 57.52 57.52 1998 46 57.52 57.52 1998 47 57.72 57.72 1998 48 172.56 172.56 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1998 50 28.76 28.76 1998 51 58.32 58.32 1998 52 86.38 86.38 1999 1 53.52 53.52 1999 3 94.6 94.6 1999 4 40.5 40.5 1999 5 80.01 80.01 1999 6 40.5 40.5 1999 8 103.11 103.11 1999 9 53.34 53.34 1999 10 72 72 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1999 11 46.92 46.92 1999 12 59.16 59.16 1999 13 27.05 27.05 1999 14 27.05 27.05 1999 15 135.1 135.1 1999 16 94.6 94.6 1999 17 148.12 148.12 1999 18 72.18 72.18 1999 20 87.42 87.42 1999 21 40.5 40.5 1999 22 107.44 107.44 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1999 25 107.44 107.44 1999 26 52.9 52.9 1999 27 53.08 53.08 1999 28 26.45 26.45 1999 31 40.87 40.87 1999 33 26.45 26.45 1999 34 105.8 105.8 1999 37 105.8 105.8 1999 38 72.3 72.3 1999 39 93.4 93.4 1999 40 40.5 40.5 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 1999 42 120.59 120.59 1999 43 52.9 52.9 1999 44 130.72 130.72 1999 45 26.45 26.45 1999 46 26.45 26.45 1999 47 147.78 147.78 1999 48 20.25 20.25 1999 53 27.05 27.05 2000 1 46.7 46.7 2000 3 93.41 93.41 2000 4 46.54 46.54 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2000 5 46.7 46.7 2000 7 70.8 70.8 2000 8 46.54 46.54 2000 11 93.74 93.74 2000 12 46.54 46.54 2000 13 117.5 117.5 2000 14 117.67 117.67 2000 15 70.47 70.47 2000 16 94.4 94.4 2000 18 93.74 93.74 2000 19 46.54 46.54 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2000 20 47.2 47.2 2000 21 187.48 187.48 2000 22 116.51 116.51 2000 23 23.6 23.6 2000 24 140.28 140.28 2000 27 45.08 45.08 2000 28 88.96 88.96 2000 29 66.72 66.72 2000 30 66.72 66.72 2000 31 44.78 44.78 2000 33 134.11 134.11 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2000 34 178.52 178.52 2000 35 78.82 78.82 2000 36 118.41 118.41 2000 37 117.96 117.96 2000 38 79.36 79.36 2000 39 19.84 19.84 2000 40 89.56 89.56 2000 43 179.12 179.12 2000 44 135.24 135.24 2000 45 67.62 67.62 2000 46 246.74 246.74 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2000 48 104.55 104.55 2000 49 42.38 42.38 2000 50 21.19 21.19 2000 52 67.45 67.45 2001 1 92.26 92.26 2001 2 118.38 118.38 2001 3 47.24 47.24 2001 4 256.7 256.7 2001 5 93.44 93.44 2001 6 22.44 22.44 2001 7 69.96 69.96 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2001 8 46.06 46.06 2001 9 92.67 92.67 2001 10 69.05 69.05 2001 11 71.57 71.57 2001 12 116.81 116.81 2001 13 116.81 116.81 2001 14 162.91 162.91 2001 15 91.98 91.98 2001 16 278.44 278.44 2001 18 22.37 22.37 2001 20 118.03 118.03 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2001 21 233.7 233.7 2001 22 141.78 141.78 2001 23 22.38 22.38 2001 24 136.92 136.92 2001 25 139.28 139.28 2001 27 94.48 94.48 2001 29 116.85 116.85 2001 30 162.91 162.91 2001 31 92.21 92.21 2001 32 68.9 68.9 2001 33 115.52 115.52 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2001 34 68.9 68.9 2001 36 91.12 91.12 2001 37 93.16 93.16 2001 38 139 139 2001 39 115.57 115.57 2001 40 45.18 45.18 2001 41 67.19 67.19 2001 42 136.98 136.98 2001 43 139.58 139.58 2001 44 23.29 23.29 2001 46 93.58 93.58 CALENDAR_YEAR WEEK SALE SUM(SALE) ------------- ---------- ---------- ---------- 2001 48 182.96 182.96 2001 49 45.26 45.26 2001 50 23.14 23.14 2001 51 114.82 114.82 2001 52 23.14 23.14 159 rows selected.复制
这是书里用partition by实现的查询，不明白这两者有什么区别，求大神指教一二：
1 select calendar_year,week,sale,sum(sale) over( 2 partition by product,country,region,calendar_year 3 order by week 4 rows between unbounded preceding and current row 5 ) running_sum_ytd 6 from sales_fact 7 where country in ('Australia') and product='Xtend Memory' 8* order by product,country,calendar_year,week SQL> / CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1998 1 58.15 58.15 1998 2 29.39 87.54 1998 3 29.49 117.03 1998 4 29.49 146.52 1998 5 29.8 176.32 1998 6 58.78 235.1 1998 9 58.78 293.88 1998 10 117.76 411.64 1998 12 59.6 471.24 1998 14 58.78 530.02 1998 15 58.78 588.8 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1998 17 58.78 647.58 1998 18 117.56 765.14 1998 19 58.98 824.12 1998 21 59.6 883.72 1998 23 117.56 1001.28 1998 26 117.56 1118.84 1998 27 57.52 1176.36 1998 28 57.72 1234.08 1998 29 57.72 1291.8 1998 34 115.44 1407.24 1998 35 57.52 1464.76 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1998 38 115.84 1580.6 1998 39 115.84 1696.44 1998 40 57.52 1753.96 1998 41 58.32 1812.28 1998 42 115.84 1928.12 1998 43 57.52 1985.64 1998 44 57.52 2043.16 1998 45 57.52 2100.68 1998 46 57.52 2158.2 1998 47 57.72 2215.92 1998 48 172.56 2388.48 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1998 50 28.76 2417.24 1998 51 58.32 2475.56 1998 52 86.38 2561.94 1999 1 53.52 53.52 1999 3 94.6 148.12 1999 4 40.5 188.62 1999 5 80.01 268.63 1999 6 40.5 309.13 1999 8 103.11 412.24 1999 9 53.34 465.58 1999 10 72 537.58 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1999 11 46.92 584.5 1999 12 59.16 643.66 1999 13 27.05 670.71 1999 14 27.05 697.76 1999 15 135.1 832.86 1999 16 94.6 927.46 1999 17 148.12 1075.58 1999 18 72.18 1147.76 1999 20 87.42 1235.18 1999 21 40.5 1275.68 1999 22 107.44 1383.12 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1999 25 107.44 1490.56 1999 26 52.9 1543.46 1999 27 53.08 1596.54 1999 28 26.45 1622.99 1999 31 40.87 1663.86 1999 33 26.45 1690.31 1999 34 105.8 1796.11 1999 37 105.8 1901.91 1999 38 72.3 1974.21 1999 39 93.4 2067.61 1999 40 40.5 2108.11 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 1999 42 120.59 2228.7 1999 43 52.9 2281.6 1999 44 130.72 2412.32 1999 45 26.45 2438.77 1999 46 26.45 2465.22 1999 47 147.78 2613 1999 48 20.25 2633.25 1999 53 27.05 2660.3 2000 1 46.7 46.7 2000 3 93.41 140.11 2000 4 46.54 186.65 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2000 5 46.7 233.35 2000 7 70.8 304.15 2000 8 46.54 350.69 2000 11 93.74 444.43 2000 12 46.54 490.97 2000 13 117.5 608.47 2000 14 117.67 726.14 2000 15 70.47 796.61 2000 16 94.4 891.01 2000 18 93.74 984.75 2000 19 46.54 1031.29 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2000 20 47.2 1078.49 2000 21 187.48 1265.97 2000 22 116.51 1382.48 2000 23 23.6 1406.08 2000 24 140.28 1546.36 2000 27 45.08 1591.44 2000 28 88.96 1680.4 2000 29 66.72 1747.12 2000 30 66.72 1813.84 2000 31 44.78 1858.62 2000 33 134.11 1992.73 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2000 34 178.52 2171.25 2000 35 78.82 2250.07 2000 36 118.41 2368.48 2000 37 117.96 2486.44 2000 38 79.36 2565.8 2000 39 19.84 2585.64 2000 40 89.56 2675.2 2000 43 179.12 2854.32 2000 44 135.24 2989.56 2000 45 67.62 3057.18 2000 46 246.74 3303.92 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2000 48 104.55 3408.47 2000 49 42.38 3450.85 2000 50 21.19 3472.04 2000 52 67.45 3539.49 2001 1 92.26 92.26 2001 2 118.38 210.64 2001 3 47.24 257.88 2001 4 256.7 514.58 2001 5 93.44 608.02 2001 6 22.44 630.46 2001 7 69.96 700.42 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2001 8 46.06 746.48 2001 9 92.67 839.15 2001 10 69.05 908.2 2001 11 71.57 979.77 2001 12 116.81 1096.58 2001 13 116.81 1213.39 2001 14 162.91 1376.3 2001 15 91.98 1468.28 2001 16 278.44 1746.72 2001 18 22.37 1769.09 2001 20 118.03 1887.12 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2001 21 233.7 2120.82 2001 22 141.78 2262.6 2001 23 22.38 2284.98 2001 24 136.92 2421.9 2001 25 139.28 2561.18 2001 27 94.48 2655.66 2001 29 116.85 2772.51 2001 30 162.91 2935.42 2001 31 92.21 3027.63 2001 32 68.9 3096.53 2001 33 115.52 3212.05 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2001 34 68.9 3280.95 2001 36 91.12 3372.07 2001 37 93.16 3465.23 2001 38 139 3604.23 2001 39 115.57 3719.8 2001 40 45.18 3764.98 2001 41 67.19 3832.17 2001 42 136.98 3969.15 2001 43 139.58 4108.73 2001 44 23.29 4132.02 2001 46 93.58 4225.6 CALENDAR_YEAR WEEK SALE RUNNING_SUM_YTD ------------- ---------- ---------- --------------- 2001 48 182.96 4408.56 2001 49 45.26 4453.82 2001 50 23.14 4476.96 2001 51 114.82 4591.78 2001 52 23.14 4614.92 159 rows selected.复制
我来答
添加附件
收藏
分享
问题补充
6条回答
默认
最新
你这第4列，两个数据结果不一样啊，当然不是等价的。
第一个sql，第三列和第四列结果一样，可以判断你表里能根据calendar_year,week,sale这三个字段确定唯一一行，因此这个sum和group by没有意义，这个sql其实等价于
select calendar_year,week,sale,sale
from sales_fact
where country in ('Australia') and product='Xtend Memory'
order by CALENDAR_YEAR,week,sale
复制第二个sql，使用的是滑动窗口的开窗函数，第4个字段的含义是从窗口的第一行累加到当前行
评论
有用 0 打赏 0
partition by product,country,region,calendar_year 是分区的作用，但是你条件里又加了限定 where country in ('Australia') and product='Xtend Memory'，相当于可以简化为 partition by region,calendar_year, 接下来要看region字段的作用和里面值的分布了，而且第二条sql也没用上。
评论 有用 0 打赏 0
回答交流
Markdown
请输入正文
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
墨值悬赏