Блог

У меня есть таблица с более чем 200 миллионами записей, и я пытаюсь получить результат в одном запросе, и на выполнение следующего запроса ушло около 1,5 часов.

Данные моей таблицы:

  --------- ------------ ----- ----- ----- 
| cust_id | product_id | p_a | p_b | p_c |
 --------- ------------ ----- ----- ----- 
|       1 | a          |   1 |   0 |   0 |
|       1 | b          |   0 |   2 |   0 |
|       1 | a          |   1 |   0 |   0 |
|       1 | c          |   0 |   0 |   3 |
|       2 | a          |   1 |   0 |   0 |
|       2 | c          |   0 |   0 |   3 |
|       2 | c          |   0 |   0 |   3 |
|       3 | b          |   0 |   1 |   0 |
 --------- ------------ ----- ----- ----- 
  

Мне нужен следующий результат:

  ---------------- ----- ----- ----- ------ ------- 
| No.ofcustomers | p_a | p_b | p_c | P_ab | P_abc |
 ---------------- ----- ----- ----- ------ ------- 
|              3 |   0 |   1 |   0 |    0 |     1 |
 ---------------- ----- ----- ----- ------ ------- 
  

p_a возвращает количество клиентов, которые покупают только продукт a. Аналогично, p_b, p_c, p_ab, p_abc.

До сих пор я написал следующий запрос, который дает мне результат. Но есть ли какой-либо способ, которым я мог бы уменьшить время, затрачиваемое на выполнение запроса. Я имею в виду, что 1,5 часа — это реально очень много для любого запроса.

 WITH CTE AS
       (
    SELECT CUST_ID, SUM(P_A) AS PA, SUM(P_B) AS PB, SUM(P_C) AS PC
      FROM TABLE
      GROUP BY CUST_ID), CTE1 AS
       (
    SELECT COUNT(DISTINCT CUST_ID) AS NOOFCUSTOMERS, 
        COUNT(DISTINCT (CASE WHEN PA >= 1 AND PB = 0 AND PC = 0 THEN CUST_ID END)) AS P_A, 
        COUNT(DISTINCT (CASE WHEN PA = 0 AND PB >= 1 AND PC = 0 THEN CUST_ID END)) AS P_B, 
        COUNT(DISTINCT (CASE WHEN PA = 0 AND PB = 0 AND PC >= 1 THEN CUST_ID END)) AS P_C, 
        COUNT(DISTINCT (CASE WHEN PA >= 1 AND PB >= 1 AND PC = 0 THEN CUST_ID END)) AS P_AB, 
        COUNT(DISTINCT (CASE WHEN PA <> 0 AND PB <> 0 AND PC <> 0 THEN CUST_ID END)) AS P_ABC
      FROM CTE)
  SELECT *
    FROM CTE1;
  

Вы можете упростить запрос до:

 WITH CTE AS (
      SELECT CUST_ID, SUM(P_A) AS PA, SUM(P_B) AS PB, SUM(P_C) AS PC
      FROM TABLE
      GROUP BY CUST_ID
     )
SELECT COUNT(*) AS NOOFCUSTOMERS, 
       SUM(CASE WHEN PA >= 1 AND PB = 0 AND PC = 0 THEN 1 ELSE 0 END) AS P_A, 
       SUM(CASE WHEN PA = 0 AND PB >= 1 AND PC = 0 THEN 1 ELSE 0 END) AS P_B, 
       SUM(CASE WHEN PA = 0 AND PB = 0 AND PC >= 1 THEN 1 ELSE 0 END) AS P_C, 
       SUM(CASE WHEN PA >= 1 AND PB >= 1 AND PC = 0 THEN 1 ELSE 0 END) AS P_AB, 
       SUM(CASE WHEN PA <> 0 AND PB <> 0 AND PC <> 0 THEN 1 ELSE 0 END) AS P_ABC
FROM CTE;
  

Первый CTE суммируется по идентификатору клиента, поэтому они уже уникальны. COUNT(DISTINCT) дороже, чем другие функции агрегирования, так что это может снизить производительность.