SQL每日一题(20201103)每种商品最近一天的日平均单价

SQL每日一题(20210421)
SQL每日一题(20210805)
SQL每日一题(20211224)
SQL每日一题(20220518)

/*
我的思路:
1.先根据商品和日期分组 得到商品*数量 的sum 在除以数量的sum 得到平均单价, 然后在row_number 根据商品分组 日期排序
2.在用max over 获取rn的最大值 根据商品分组
3.最后rn=max_rn 得到最近一天的数据
*/

select * from (
                  select *, max(rn) over (partition by a.商品 ) max_rn
                  from (
                           select a.商品,
                                  a.日期,
                                  round(sum(a.单价 * a.数量) / sum(a.数量), 2)              as avg_price,
                                  row_number() over (partition by a.商品 order by a.日期) as rn
                           from t1103 a
                           group by a.商品, a.日期
                       ) a
              ) a
where a.rn=max_rn
order by a.日期

/*
1.用dense_rank 根据商品分组 日期倒序 得到最近的日期 ranks
2.sum 单价*数量 除以 sum 数量得到平均价格 用cast decimal(8,2) 可以转化为2位小数 ranks=1 得到最近的日期 再根据商品分组
*/

select a.商品, cast(sum(a.单价 * a.数量) / SUM(a.数量) as decimal(8, 2)) as 平均价格
from (select 商品, 单价, 数量, 日期, dense_rank() over (partition by 商品 order by 日期 desc) as ranks from T1103) a
where a.ranks = 1
group by a.商品

/*
1.max 根据商品分组 得到最大的日期 temp表
2.得到 SUM(a.数量 * a.单价) / SUM(a.数量) 平均价格 根据商品和日期分组 inner join temp表 让他们的商品和日期相连 这样就得到了最新的日期
*/

select a.商品, a.日期, ROUND(SUM(a.数量 * a.单价) / SUM(a.数量), 2) as 平均价格
from t1103 a
         inner join (select 商品, MAX(日期) as 最新日期 from t1103 group by 商品) b on a.商品 = b.商品 and a.日期 = b.最新日期
group by a.商品, a.日期 order by a.日期;