Задать вопрос

mayerilya

Лайки

  • Как объединить три таблицы, чтобы данные из двух были в одном столбце?

    tsklab
    @tsklab Куратор тега SQL Server
    Здесь отвечаю на вопросы.
    Source, Campaign, Ad — это составной ключ (для source any — внешний).
    Делаете UNION ALL по всем таблицам, а полученные результат группируете по Source, Campaign, Ad.
    Тестовые данные:
    DECLARE @SA TABLE ( [Date] DATE, Campaign VARCHAR(100), Ad VARCHAR(100), 
                     Impression INT, Click INT, Cost MONEY );
INSERT @SA VALUES 
('2022-11-08', 'Vasya',	'C', 12, 1,	1),
('2022-11-08', 'Vasya',	'C', 12, 1,	1),
('2022-11-08', 'A',	'C', 12, 1,	1),
('2022-11-08', 'A',	'C', 12, 1,	1),
('2022-11-08', 'B',	'A', 12, 1,	1),
('2022-11-08', 'B',	'A', 12, 1,	1),
('2022-10-08', 'Vasya',	'C', 12, 1,	1);

DECLARE @SB TABLE ( [DateTime] DATETIME, Campaign VARCHAR(100), Ad VARCHAR(100), 
                    Impression INT, Click INT, Cost MONEY );
INSERT @SB VALUES 
('2022-11-06 12:10:00.000', 'Random',	'V', 12, 1,	1),
('2022-11-06 12:10:00.000', 'Random',	'V', 12, 1,	1),
('2022-11-07 12:10:00.000', 'H',	'A', 12, 1,	1),
('2022-11-07 12:10:00.000', 'H',	'A', 12, 1,	1),
('2022-11-08 12:10:00.000', 'K',	'C', 12, 1,	1),
('2022-11-08 12:10:00.000', 'K',	'C', 12, 1,	1),
('2022-11-06 12:10:00.000', 'Random', 'V', 12, 2,	2);

DECLARE @Sany TABLE ( [Date] DATE, [Source] VARCHAR(100), Campaign VARCHAR(100), Ad VARCHAR(100), 
                      Install INT, Purchase MONEY );
INSERT @Sany VALUES 
('2022-11-08', 'Source A', 'Vasya',	'C', 1,	1),
('2022-11-08', 'Source A', 'Vasya',	'C', 1,	1),
('2022-11-08', 'Source A', 'A',	'C', 1,	1),
('2022-11-08', 'Source A', 'A',	'C', 1,	1),
('2022-11-08', 'Source A', 'B',	'A', 1,	1),
('2022-11-08', 'Source A', 'B',	'A', 1,	1),
('2022-11-06', 'Source B', 'Random',	'V', 1,	1),
('2022-11-06', 'Source B', 'Random',	'V', 1,	1),
('2022-11-07', 'Source B', 'H',	'A', 1,	1),
('2022-11-08', 'Source A', 'Vasya',	'C', 3,	3),
('2022-11-08', 'Source A', 'Vasya',	'C', 1,	1),
('2022-11-08', 'Source A', 'Vasya',	'C', 1,	1),
('2022-11-11', 'Source A', 'Vasya',	'C', 2,	2),
('2022-11-13', 'Source C', 'AAAA',	'B', 2,	2);

    SELECT [Source], Campaign, Ad, 
       SUM( Click ) AS SumClick, SUM( Cost ) AS SumCost,
       SUM( Install ) AS SumInstall, SUM( Purchase) AS SumPurchase
  FROM (
SELECT 'Source A' AS [Source], Campaign, Ad, Click, Cost, 
       CAST(NULL AS INT) AS [Install], CAST(NULL AS MONEY) AS Purchase
  FROM @SA
UNION ALL
SELECT 'Source B' AS [Source], Campaign, Ad, Click, Cost, 
       CAST(NULL AS INT) AS [Install], CAST(NULL AS MONEY) AS Purchase
  FROM @SB
UNION ALL
SELECT [Source], Campaign, Ad, NULL, NULL, [Install], Purchase
  FROM @Sany
       ) AS Common
  GROUP BY [Source], Campaign, Ad
    Ответ написан
    9 комментариев
    9 комментариев