欢迎访问 生活随笔！
TAG聚合

生活随笔

当前位置： 首页 > 编程资源 > 编程问答 >内容正文

编程问答

sql 精读(六)获取每个类别中最受欢迎的商品

发布时间：2025/4/5 编程问答 46 豆豆
生活随笔 收集整理的这篇文章主要介绍了 sql 精读(六)获取每个类别中最受欢迎的商品 小编觉得挺不错的,现在分享给大家,帮大家做个参考.

统计某一类别最受欢迎的商品

WITH Produce AS(SELECT 'kale' as item, 23 as purchases, 'vegetable' as categoryUNION ALL SELECT 'orange', 2, 'fruit'UNION ALL SELECT 'cabbage', 9, 'vegetable'UNION ALL SELECT 'apple', 8, 'fruit'UNION ALL SELECT 'leek', 2, 'vegetable'UNION ALL SELECT 'lettuce', 10, 'vegetable') SELECT item, purchases, category, LAST_VALUE(item)OVER (PARTITION BY categoryORDER BY purchasesROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS most_popular FROM Produce
  • fruit
  • (orange, apple) = apple is most popular
  • (orange, apple) = apple is most popular
  • vegetable
  • (leek, cabbage, lettuce, kale) = leek is most popular
  • (leek, cabbage, lettuce, kale) = lettuce is most popular
  • (leek, cabbage, lettuce, kale) = kale is most popular
  • (leek, cabbage, lettuce, kale) = kale is most popular

    统计某一类别窗口内最受欢迎的商品，本窗口长度是四【前一，本身，后一，后２】
WITH Produce AS(SELECT 'kale' as item, 23 as purchases, 'vegetable' as categoryUNION ALL SELECT 'orange', 2, 'fruit'UNION ALL SELECT 'cabbage', 9, 'vegetable'UNION ALL SELECT 'apple', 8, 'fruit'UNION ALL SELECT 'leek', 2, 'vegetable'UNION ALL SELECT 'lettuce', 10, 'vegetable') SELECT item, purchases, category, LAST_VALUE(item)OVER (PARTITION BY categoryORDER BY purchasesROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS most_popular FROM Produce

《新程序员》：云原生和全面数字化实践50位技术专家共同创作，文字、视频、音频交互阅读

总结

以上是生活随笔为你收集整理的sql 精读(六)获取每个类别中最受欢迎的商品的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。