Самые популярные пары магазинов для работников от каждой компании

У меня есть 2 таблицы, одна с продажами и одна с компаниями:

Таблица продаж

Transaction_Id   Shop_id     Sale_date    Client_ID

92356            24234       11.09.2018    12356
92345            32121       11.09.2018    32121
94323            24321       11.09.2018    21231
94278            45321       11.09.2018    42123

Стол компании

Client_ID  Company_name 

12345      ABC 
13322      ABC  
32321      BCD
22221      BCD

Чего я хочу добиться, так это отдельного количества Клиентов от каждой Компании для каждой пары магазинов (Клиенты, у которых была хотя бы одна транзакция в обоих магазинах):

Shop_Id_1     Shop_id_2   Company_name    Count(distinct Client_id)

12356         12345       ABC             31
12345         14278       ABC             23
14323         12345       BCD             32
14278         12345       BCD             43

Я думаю, что мне нужно использовать самосоединение, но мои запросы, даже с фильтром на одну неделю, убивают БД, есть мысли по этому поводу? Я использую Microsoft SQL Server 2012.

Спасибо

Источник

2 ответа

Решение

Я думаю, что это самостоятельное объединение и объединение, с изюминкой. Суть в том, что вы хотите включить компанию в каждом sales запись, так что это может быть использовано при самостоятельном объединении:

with sc as (
      select s.*, c.company_name
      from sales s join
           companies c
           on s.client_id = c.client_id
     )
select sc1.shop_id, sc2.shop_id, sc1.company_name, count(distinct sc1.client_id)
from sc sc1 join
     sc sc2
     on sc1.client_id = sc2.client_id and
        sc1.company_name = sc2.company_name
group by sc1.shop_id, sc2.shop_id, sc1.company_name;
Источник

Я думаю, что есть некоторые проблемы с вашим вопросом. Я интерпретировал это так, что таблица компании содержит идентификаторы магазина, а не ClienId.

Сначала вы можете создать решение, чтобы получить магазины в виде строк для каждой компании. Здесь я выбрал максимум 5 магазинов на компанию. Не забывайте точку с запятой в предыдущем выражении перед cte.

WITH CTE_Comp AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CompanyName ORDER BY ShopID) AS RowNumb 
    FROM Company AS C
)

SELECT C1.ShopID, 
      C2.ShopID AS ShopID_2, 
      C3.ShopID AS ShopID_3, 
      C4.ShopID AS ShopID_4, 
      C5.ShopID AS ShopID_5, 
      C1.CompanyName
INTO ShopsByCompany
FROM CTE_Comp AS C1
    LEFT JOIN CTE_Comp AS C2 ON C1.CompanyName= C2.CompanyName AND RowNumb = 2 
    LEFT JOIN CTE_Comp AS C2 ON C1.CompanyName= C3.CompanyName AND RowNumb = 3 
    LEFT JOIN CTE_Comp AS C2 ON C1.CompanyName= C4.CompanyName AND RowNumb = 4 
    LEFT JOIN CTE_Comp AS C2 ON C1.CompanyName= C5.CompanyName AND RowNumb = 5 
WHERE C1.RowNumb = 1

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

WITH ClientsPerShop AS 

(
    SELECT ShopID,
         COUNT (DISTINCT ClientID) AS TotalClients
    FROM Sales
    GROUP BY ShopID
)

, ClienstsPerCompany AS 

(
    SELECT CompanyName,
         SUM (TotalClients) AS ClientsPerComp
    FROM Company AS C
    INNER JOIN ClientsPerShop AS CPS ON C.ShopID = CPS.ShopID
    GROUP BY CompanyName
)

SELECT * 
FROM ClienstsPerCompany AS CPA
INNER JOIN ShopsByCompany AS SBC ON SBC.CompanyName = CPA.CompanyName

Надеюсь, это приблизит вас к вашему решению, удачи!

Источник
Другие вопросы по тегам