sql 窗口查询
原创
©著作权归作者所有:来自51CTO博客作者luoganttcc的原创作品,请联系作者获取转载授权,否则将追究法律责任
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 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
等价与下面
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 (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)