Блог

Я безуспешно пытаюсь (и терплю неудачу) придумать запрос mysql, чтобы вычислить, сколько раз песня ранее занимала одну и ту же позицию в чарте в предыдущие недели подряд. Так, например, учитывая приведенный ниже набор данных, как бы я написал запрос (на основе указания даты), который возвращает:

• название песни
• Дата
• Позиция в чарте
• количество предыдущих недель, в течение которых он находился в том же положении (дополнительные очки брауни, если ответ равен 0, означают, что он вырос или упал на графике)

Итак, учитывая простой выбор:

SELECT song_name, date, chart_position FROM table WHERE date = '2019-12-29' ORDER BY chart_position ASC

Мы должны получить следующий результат:

Однако, что нужно добавить дополнительную информацию, чтобы сделать это:

Любая помощь очень ценится, поскольку я провел последние 6 часов, пытаясь разобраться в себе с большим количеством поисковых запросов в Интернете и не смог с этим справиться! Спасибо вам за уделенное время.

Скорее всего, некоторые оптимизации все еще можно выполнить, но следующие дадут вам весь результат, который вы запросили. Первая часть (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.

Вы можете добавить новый столбец в свою таблицу для простого запроса, например, previous_chart_position типа integer (простое сравнение для same / new / up / down) и chart_position_updatedat datetime, чтобы вычислить разницу во времени для weeks_in_position .