Запрос MySQL для подсчета событий в дни года

#mysql #date #group-by #count

#mysql #Дата #группировка по #подсчет

Вопрос:

У меня есть (большая) таблица, подобная этой:

 id     venue               city        date time
1      Waldorf Hotel       London      2020-01-01 07:00  
2      Waldorf Hotel       London      2020-01-02 07:00  
3      Heathrow            London      2020-01-02 14:00  
4      Lennon Airport      Liverpool   2020-01-02 16:00  
5      Port of Liverpool   Liverpool   2020-01-02 19:30  
6      Port of Liverpool   Liverpool   2020-01-03 07:00  
7      Port of Liverpool   Liverpool   2020-01-04 07:00  
8      Port of Liverpool   Liverpool   2020-01-05 07:00  
9      Port of Liverpool   Liverpool   2020-01-06 07:00  
10     Manchester Airport  Manchester  2020-01-06 12:40  
11     Heathrow            London      2020-01-06 14:40

Итак, этот человек был в Ливерпуле 5 разных дней, в Лондоне 3 разных дня и в Манчестере только 1 день.

Я ищу запрос, который дает мне это количество дней, в течение которых был посещен определенный город, в результате чего получается этот список:

     Liverpool      5
    London         3
    Manchester     1

Но теперь я застрял в этом запросе, который подсчитывает вхождения в день, и я не могу понять это правильно:

     SELECT city, COUNT(city) AS value FROM visits WHERE YEAR(dt) = 2020 GROUP BY city, DATE(dt)

Ответ №1:

Один из способов добиться этого — выбрать все различные комбинации город / дата в подзапросе, а затем подсчитать количество встречаемости каждого города:

 SELECT city, COUNT(city) AS value 
FROM (
  SELECT DISTINCT city, DATE(dt)
  FROM visits
  WHERE YEAR(dt) = 2020 
) v
GROUP BY city

Вывод:

 city        value
Liverpool   5
London      3
Manchester  1

Демонстрация на SQLFiddle