Блог

Допустим, у меня есть таблица с 3 столбцами:

Столбец A: Client_ID (не уникальный, может отображаться в нескольких строках)

Столбец B: Цена, уплаченная за продукт

Столбец C: Продукт (тип)

  --- ---- ------ --- 
|   | A  |  B   | C |
 --- ---- ------ --- 
| 1 | 5  | 1200 | 1 | 
| 2 | 12 | 10   | 1 | 
| 3 | 12 | 15   | 1 | 
| 4 | 12 | 1750 | 2 | 
| 5 | 12 | 1250 | 2 | 
| 6 | 18 | 10   | 1 | 
| 7 | 18 | 1500 | 1 | 
| 8 | 18 | 1050 | 2 |
| 9 | 18 | 1050 | 2 |
 --- ---- ------ --- 
  

Я хочу получить 6 столбцов:

 Client_ID | Product | Number_of_transactions | Sum_of_transactions | Number_of_transactions_over_1000 | Sum_of_transactions_over_1000
  

то есть

 | Client_ID | Product | Nb_tr | Sum_tr | Nb_tr_1000 | Sum_tr_1000 |
 ----------- --------- ------- -------- ------------ ------------- 
|         5 |       1 |     1 |   1200 |          1 |        1200 |
|         5 |       2 |     0 |      0 |          0 |           0 |
|        12 |       1 |     2 |     25 |          0 |           0 |
|        12 |       2 |     2 |   3000 |          2 |        3000 |
|        18 |       1 |     2 |   1510 |          1 |        1500 |
|        18 |       2 |     2 |   2100 |          2 |        2100 |
 ----------- --------- ------- -------- ------------ ------------- 
  

С Client_ID x Продукт уникален

Я думаю, что первая часть может быть достигнута с помощью простой ГРУППЫ ПО

 (SELECT ...
SUM(B) as Sum_of_transactions, COUNT(B) as Number_of_transactions 
...
GROUP BY A, C)
  

Однако, как я могу также получить Number_of_transactions_over_1000 и Sum_of_transactions_over_1000? Я знаю, что могу получить их с помощью отдельного запроса (путем добавления, где B> 1000), и я могу впоследствии объединить запрос для Number_of_transactions amp; Sum_of_transactions с запросом для Number_of_transactions_over_1000 amp; Sum_of_transactions_over_1000, но можно ли это сделать в одном запросе (с помощью одной инструкции ВЫБОРА)?

Примечание: Это не приведет к экстраполяции product_type, но даст правильные результаты. Ваша таблица входных данных называется data_p в следующем запросе, измените соответствующим образом. Также посмотрите, поддерживает ли SAS операцию CASE WHEN.[Из-за отсутствия у меня базы данных SAS в реальном времени, я не смог проверить это в SAS, но этот SQL достаточно общий.]

 SELECT
  clientId,
  p_type,
  SUM(price) as sum_of_tr,
  COUNT(*) as num_of_tr,
  SUM(CASE WHEN price>1000 THEN price ELSE 0 END) as sum_of_tr_over_1000,
  SUM(CASE WHEN price>1000 THEN 1     ELSE 0 END) as num_of_tr_over_1000 
FROM
  data_p
GROUP BY
  clientId, p_type
ORDER BY
 clientId, p_type
  

Вы ищете условную агрегацию ( CASE WHEN внутри функции агрегирования).

 SELECT
  client_id,
  product,
  count(*) as nb_tr,
  sum(price) as sum_tr,
  count(case when price > 1000 then 1 end) as nb_tr_1000,
  sum(case when price > 1000 then price end) as sum_tr_1000
from purchases
group by client_id, product
order by client_id, product;
  

Ваш вывод показывает, что вы хотите получить результаты для всех комбинаций Client_ID x Product , даже если комбинация не существует в данных (т. Е. 0 отсчетов). Например, в случае 5 x 2

иметь

  --- ---- ------ --- 
|   | A  |  B   | C |
 --- ---- ------ --- 
| 1 | 5  | 1200 | 1 | 
  

хотите

 | Client_ID | Product | Nb_tr | Sum_tr | Nb_tr_1000 | Sum_tr_1000 |
 ----------- --------- ------- -------- ------------ ------------- 
|         5 |       1 |     1 |   1200 |          1 |        1200 |
|         5 |       2 |     0 |      0 |          0 |           0 |
  

Итак, важный промежуточный вопрос: «откуда берутся разные значения product?

В большом наборе данных можно найти все возможные product поверх всех client_id . Но тогда вы можете и не делать этого.

Самый надежный способ гарантировать наличие всех продуктов — это иметь отдельную таблицу продуктов, однако перекрестное объединение таблицы продуктов с исходной таблицей (сгруппированной по product_id ) может привести к результату, превышающему желаемый. В некоторых процедурах SAS с помощью classdata= опции можно указать отдельную таблицу категориальных комбинаций, подлежащих обработке.

Второй способ — подумать о комбинаторном покрытии в исходной таблице. Т.е. Отдельно выбрать и пересечь различные значения иерархических уровней, представленных в исходной таблице, т. е. охват клиента x охват продукта. В некоторых небольших или стратифицированных наборах данных может не отображаться полный список потенциальных клиентов и продуктов.

Пример (покрытие):

 data have; input 
client_id price product_id ; datalines;
5   1200  1  
12  10    1  
12  15    1  
12  1750  2  
12  1250  2  
18  10    1  
18  1500  1  
18  1050  2 
18  1050  2 
;

proc sql;
  create table want as
  select
    clients.client_id
  , products.product_id
  , coalesce(tr_count,0) as tr_count
  , coalesce(tr_sum, 0) as tr_sum
  , coalesce(tr_1000_count, 0) as tr_1000_count
  , coalesce(tr_1000_sum, 0) as tr_1000_sum
  from 
   (select distinct product_id from have) 
   as products
  cross join
   (select distinct client_id from have)
   as clients
  left join 
   (select 
        client_id
      , product_id
      , count(price) as tr_count
      , sum(price) as tr_sum
      , sum(price > 1000) as tr_1000_count
      , sum(case when price>1000 then price else 0 end) as tr_1000_sum
    from
      have
    group by 
      client_id, product_id
   )
   as summary
  on
   clients.client_id = summary.client_id
   and 
   products.product_id = summary.product_id
  ;