Объединение таблиц и сводка данных с помощью MySQL

Задача 1. Данные о ваших продажах хранятся в таблице «Покупки». Ваш торговый персонал хочет видеть данные о продажах в сводной форме с разбивкой по кварталам.

Если в вашей таблице Purchases нет данных о продажах, создайте их. Убедитесь, что данные охватывают четыре квартала. Затем напишите запрос для поворота данных следующим образом:

Album              Q1   Q2   Q3   Q4

OK Computer        2    5    3    7

 Sea Change        8    6    2    1

Не создавайте отдельную таблицу или представление. Не изменяйте никакие таблицы.

Сохраните запрос как dba1lesson10project1.sql и передайте проект.

Это то, что мне нужно сделать. Но таблица, с которой он хочет, чтобы я работал, выглядит так. А в задании написано, что я вообще не могу его изменить.

CustomerID    DateOfPurchase   SongID

1              2007-03-31        3
3              2007-06-30        4
4              2007-09-30        4
5              2007-12-31        5

Я знаю, что мне нужно объединить три таблицы вместе, чтобы я мог сгруппировать их по названию. Какие у меня таблицы «Песни», «Альбомы» и «Покупки».

 SELECT Albums.Title FROM Albums
 LEFT JOIN Songs
 INNER JOIN Purchases
 ON Songs.SongID = Purchases.SongID
 ON Albums.Title = Purchases.SongID,
 SELECT Title,
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 1 THEN 1 ELSE 0 END) AS 'Q1',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 2 THEN 1 ELSE 0 END) AS 'Q2',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 3 THEN 1 ELSE 0 END) AS 'Q3',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 4 THEN 1 ELSE 0 END) AS 'Q4'
 From Purchases
 GROUP BY Title;

Я немного в недоумении, когда дело доходит до объединения трех отдельных таблиц, а затем для поворота данных

Я пробовал приведенный выше код в нескольких других вариантах, которые не помогли мне пройти часть объединения таблиц.

Любая помощь приветствуется.


mysql join pivot
person Dylan McCann    schedule 18.09.2013    source источник
comment
Пожалуйста, покажите схемы таблиц и образцы данных для всех трех таблиц.   -  person peterm    schedule 18.09.2013

Ответы (1)


arrow_upward
1
arrow_downward

Мое предложение, прежде чем пытаться PIVOT данные, было бы сначала написать запрос, чтобы вернуть нужные вам столбцы, это потребует объединения ваших таблиц. Вы не предоставили определения своей таблицы, поэтому я делаю несколько предположений о структуре. Если ваши таблицы структурированы следующим образом:

CREATE TABLE Purchases
    (`CustomerID` int, `DateOfPurchase` datetime, `SongID` int)
;

CREATE TABLE Albums
    (`AlbumId` int, `Title` varchar(11))
;

CREATE TABLE Songs
    (`SongID` int, `AlbumID` int)
;

Затем вы должны ВЫБРАТЬ из таблиц, используя JOIN, подобный этому коду:

select a.title,
  p.DateOfPurchase
from albums a
inner join songs s
  on a.albumid = s.albumId
inner join purchases p
  on s.songid = p.songid

Этот запрос вернет вам альбом Title, а также альбом DateOfPurchase, который вам нужен для поворота данных. После того, как вы проработаете JOINS, вы можете заменить p.DateOfPurchase своей агрегатной функцией и выражением CASE и добавить предложение GROUP BY, чтобы получить окончательный результат:

select a.title,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 1 THEN 1 ElSE 0 END) AS Q1,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 2 THEN 1 ELSE 0 END) AS Q2,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 3 THEN 1 ELSE 0 END) AS Q3,
  SUM(CASE WHEN Quarter(p.DateOfPurchase) = 4 THEN 1 ELSE 0 END) AS Q4
from albums a
inner join songs s
  on a.albumid = s.albumId
inner join purchases p
  on s.songid = p.songid
group by a.title;

См. SQL Fiddle с демонстрацией. Это дает результат:

|       TITLE | Q1 | Q2 | Q3 | Q4 |
| OK Computer |  1 |  0 |  0 |  0 |
|  Sea Change |  0 |  1 |  1 |  0 |
person Taryn    schedule 18.09.2013
comment
Спасибо. Я ценю это, очень. - person Dylan McCann; 19.09.2013