Блог

У меня есть следующая таблица предложений в iTunes, Google Play и Microsoft:

 `offers`
- id
- Title
- Year
- Platform
- Offer
  

Пример записи может быть чем-то вроде:

 (1, 'Titanic', 1997, 'iTunes', 'HDBUY')
  

Это означало бы, что название фильма «Титаник» (1997) доступно для покупки HD в iTunes.

У меня несколько платформ, и я хотел бы сравнить iTunes с другими платформами, чтобы увидеть, какая платформа имеет больше предложений на название. В идеале результат моего запроса будет выглядеть следующим образом:

 Title        Year        iTunes_Offers        Top_Platform_Offers         Top_Platform
Titanic      1997        HDBUY,SDBUY          HDBUY,SDBUY,SDRENT          Microsoft
Avatar       2009                             HDBUY                       Google
  

Вот схема с образцами данных для тестирования:

 CREATE TABLE `offers` (
  `id` varchar(20) DEFAULT NULL,
  `Title` varchar(100) DEFAULT NULL,
  `Year` varchar(20) DEFAULT NULL,
  `Platform` varchar(100) DEFAULT NULL,
  `Offer` varchar(20) DEFAULT NULL
);

INSERT INTO `offers` (`id`, `Title`, `Year`, `Platform`, `Offer`)
VALUES
    ('1', 'Titanic', '1997', 'iTunes', 'HDBUY'),
    ('1', 'Titanic', '1997', 'iTunes', 'SDBUY'),
    ('2', 'Titanic', '1997', 'Microsoft', 'SDBUY'),
    ('2', 'Titanic', '1997', 'Microsoft', 'HDBUY'),
    ('2', 'Titanic', '1997', 'Microsoft', 'SDRENT'),
    ('3', 'Titanic', '1997', 'Google', 'HDBUY'),
    ('4', 'Avatar', '2009', 'Google', 'HDBUY');
  

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

 SELECT Title, Year, Platform, GROUP_CONCAT(Offer) GROUP BY id
  

Что дает мне:

 id  Title   Year    Platform    Offer   group_concat(offer)
1   Titanic 1997    iTunes  HDBUY   HDBUY,SDBUY
2   Titanic 1997    Microsoft   SDBUY   SDBUY,HDBUY,SDRENT
3   Titanic 1997    Google  HDBUY   HDBUY
4   Avatar  2009    Google  HDBUY   HDBUY
  

Но я не совсем уверен, как его объединить, чтобы я мог собрать три столбца вместе, iTunes_Offers , Top_Platform_Offers , и Top_Platform . Для группировки между платформами я хотел бы объединить их по КОНКАТУ (название, год) — например, «Психо (2005)» — это другой фильм, чем «Психо (1960)».

Как мне это сделать?

Вы можете использовать условную агрегацию:

 SELECT Title, Year, Platform,
       GROUP_CONCAT(CASE WHEN platform = 'iTunes'    THEN Offer END) AS iTunes,
       GROUP_CONCAT(CASE WHEN platform = 'Microsoft' THEN Offer END) AS Microsoft,
       GROUP_CONCAT(CASE WHEN platform = 'Google'    THEN Offer END) AS Google
FROM offers
GROUP BY CONCAT(title,year);
  

Если у вас могут быть дубликаты, тогда используйте GROUP_CONCAT(DISTINCT . . .) .