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

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

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION 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 category
    ORDER BY purchases
    ROWS 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 category
  UNION 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 category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
  ) AS most_popular
FROM Produce