我遇到了一個讓我難過的 SQL 問題，我想知道是否有人可以闡明執行此操作的最佳方法。
問題：給定表格“product_price”表示一段時間內產品的平均價格。范圍內的所有天都出現在“天”列中，該列未排序。
| 天 | 價格 ($) |
|---|---|
| 4 | 2 |
| 5 | 1 |
| 3 | 1 |
| 1 | 1 |
| 2 | 5 |
查找此時間段內產品價格的例外情況，更具體地說，查找價格飆升的日子（價格嚴格高于前一天和后一天的日子）。
第一天和最后一天永遠不會是高峰。
預期結果：輸出應按“天”升序排列
| 天 |
|---|
| 2 |
| 4 |
QUERY：我已經厭倦了這個查詢
SELECT day FROM (
SELECT
day
,prev_price - price AS pre_diff
,price - next_price AS next_diff
FROM (
SELECT
day
,price
,LEAD(price) OVER(price) AS prev_price
,LAG(price) OVER(price) AS next_price
FROM product_price
ORDER BY day ASC
) a
) b
WHERE pre_diff > 1 and next_diff > 1
ORDER by day;
更新：我想我解決了。感謝@hsnsd 的提示。如果有更好的方法來撰寫此查詢，仍然會感謝任何輸入。
SELECT day FROM (
SELECT
day
,price
,prev_price
,next_price
,price - prev_price AS pre_diff
,next_price - price AS next_diff
FROM (
SELECT
day
,price
,COALESCE(LAG(price) OVER(ORDER BY day ASC),0) AS prev_price
,COALESCE(LEAD(price) OVER(ORDER BY day ASC),0) AS next_price
FROM prices
) a
) b
WHERE (day NOT IN (SELECT MIN(day) FROM prices) AND
day NOT IN (SELECT MAX(day) FROM prices))
AND (pre_diff > 0 AND next_diff < 0)
ORDER BY "day";
uj5u.com熱心網友回復：
看起來下面應該給你想要的結果：
with s as (
select *,
Lag(Price, 1, Price) over(order by day) p,
Lead(Price, 1, Price) over(order by day) n
from prices
)
select day
from s
where p < Price and n < Price;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/480574.html
標籤：sql PostgreSQL