我正在嘗試（并且失敗）悲慘地提出一個 mysql 查詢來計算一首歌之前連續幾周位于同一圖表位置的次數。例如，給定下面的資料集，我將如何撰寫回傳的查詢（基于提供日期）：
- 歌名
- 日期
- 圖表位置
- 前幾周一直處于同一位置（如果答案為 0，則表示它在圖表中是上升還是下降，則額外加分）
| ID | 歌名 | 日期 | 圖表位置 |
|---|---|---|---|
| 1 | 跳舞女王 | 2020-01-19 | 1 |
| 2 | 想成為 | 2020-01-19 | 2 |
| 3 | 跳舞女王 | 2020-01-12 | 1 |
| 4 | 你的形狀 | 2020-01-12 | 2 |
| 5 | 致盲之光 | 2020-01-05 | 1 |
| 6 | 想成為 | 2020-01-05 | 2 |
| 7 | 致盲之光 | 2019-12-29 | 1 |
| 8 | 你的形狀 | 2019-12-29 | 2 |
| 9 | 致盲之光 | 2019-12-22 | 1 |
| 10 | 想成為 | 2019-12-22 | 2 |
所以給出一個簡單的選擇：
SELECT song_name, date, chart_position FROM table WHERE date = '2019-12-29' ORDER BY chart_position ASC
我們應該得到以下結果：
| 歌名 | 日期 | 圖表位置 |
|---|---|---|
| 致盲之光 | 2019-12-29 | 1 |
| 你的形狀 | 2019-12-29 | 2 |
但是需要添加額外的資訊來實作它：
| 歌名 | 日期 | 圖表位置 | 周位置 | 運動（可選 - 相同/新/上/下） |
|---|---|---|---|---|
| 致盲之光 | 2019-12-29 | 1 | 2 | 相同的 |
| 你的形狀 | 2019-12-29 | 2 | 1 | 新的 |
任何幫助都非常感謝，因為我花了過去 6 個小時試圖通過大量在線搜索來解決問題，但無法解決！感謝您的時間。
uj5u.com熱心網友回復：
您可以向表中添加新列以便于查詢，例如 previous_chart_position 型別整數（易于比較相同/新/向上/向下）和 chart_position_updatedat datetime 以計算周數_in_position 的時間差異。
uj5u.com熱心網友回復：
很可能仍然可以進行一些優化，但以下內容將為您提供您要求的所有輸出。第一部分（CTE）基本上用于計算歌曲在歌曲位置連續多少周。第二部分用于通過將表連接到前一周來計算與前一周相比的位置。
WITH RECURSIVE cte AS (
SELECT id, song_name, 1 as weeks_in_position, chart_position, dt
FROM charts WHERE dt='2019-12-29'
UNION ALL
SELECT charts.id, charts.song_name, cte.weeks_in_position 1, charts.chart_position, charts.dt
FROM cte
INNER JOIN charts ON charts.song_name = cte.song_name
AND charts.chart_position = cte.chart_position
AND cte.id <> charts.id
AND DATEDIFF(cte.dt, charts.dt) <= 7
AND DATEDIFF(cte.dt, charts.dt) > 0
)
SELECT * FROM (
SELECT cte.song_name, cte.dt,
MAX(cte.weeks_in_position) OVER(PARTITION BY song_name) weeks_in_position,
CASE
WHEN charts.dt IS NULL THEN 'new'
WHEN cte.chart_position > charts.chart_position THEN 'up'
WHEN cte.chart_position < charts.chart_position THEN 'down'
ELSE 'same'
END AS movement
FROM cte
LEFT JOIN charts
ON cte.song_name = charts.song_name
AND DATE_ADD(charts.dt, INTERVAL 7 DAY)=cte.dt
) AS DATA
WHERE dt='2019-12-29'
您可以在此db fiddle 中檢查結果。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/328823.html
下一篇：輸出時的隨機值