本文深入探讨了一段SQL查询语句，该语句用于从特定日期的城市商品分布数据中筛选并获取排名前50的商品信息。通过使用窗口函数实现排名计算，并结合条件过滤，展示了如何高效地从大数据集提取关键信息。
SELECT
atdate, #业务日期
city_id, #城市id
goods_id, #商品id
rank #排名
FROM
(
SELECT
oe.atdate,oe.city_id,oe.goods_id,oe.use_amt,
@rownum := @rownum+1,
IF(@city_id = oe.city_id , @rank :=@rank + 1 ,@rank := 1) AS rank, #排名
@city_id := oe.city_id
FROM(
SELECT atdate,city_id,goods_id,use_amt FROM dw.t_rpt_city_goods_distri_monitor_d t1 WHERE t1.`atdate`='2016-02-15'
ORDER BY city_id ASC,IFNULL(use_amt,0) DESC
) oe,
( SELECT @rank := 0, @city_id := NULL, @rownum := 0) rk
)m WHERE m.city_id=2 and rank<=50
mysql 求分组top
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
