RFM анализ. подсчет общего кол-ва пользователей и суммы

Question

Как правильно вывести общую сумму по полю price и count(distinct user_id) из таблицы

SELECT o.user_id, count(id_o), SUM(price), datediff('2018-01-01', DATE_FORMAT(max(o_date),"%Y-%m-%d")) as days, 
CASE WHEN datediff('2018-01-01', DATE_FORMAT(max(o_date),"%Y-%m-%d")) <= 30 THEN "3"
  WHEN datediff('2018-01-01', DATE_FORMAT(max(o_date),"%Y-%m-%d")) BETWEEN 31 AND 60 THEN "2"
     ELSE "1"
END AS R,
CASE WHEN COUNT(id_o) >= 5 THEN "3"
  WHEN COUNT(id_o) >= 2 AND COUNT(id_o) < 5 THEN "2"
     ELSE "1"
 END AS F,
t.M
FROM shop_1.orders_lessons_1 as o
join
(SELECT user_id,
CASE WHEN sum(price) >= 20000 THEN "3"
  WHEN sum(price) < 20000 and sum(price) >= 10000 THEN "2"
     ELSE "1"
END AS M   
FROM shop_1.orders_lessons_1 group by user_id) as t
on o.user_id = t.user_id
group by user_id
having R != "1" and F != "3" and t.M != "3"
order by user_id;

я пыталась сделать таким образом:

    SELECT count(distinct user_id), sum(price) 
    from shop_1.orders_lessons_1 as shop
    join
    (табл.выше) as tabl
    on shop.user_id = tabl.user_id
having R != "1" and F != "3" and t.M != "3";

но выдает ошибку

 50000  Error Code: 1052. Column 'user_id' in field list is ambiguous   0,035 sec

Answer 1

Добавила алиас, как рекомендовали. Вот полный код, возможно кому-нибудь пригодится

SELECT count(distinct shop.user_id), sum(shop.price) 
from shop_1.orders_lessons_1 as shop
join
(SELECT o.user_id, count(id_o), SUM(price), datediff('2018-01-01', DATE_FORMAT(max(o_date),"%Y-%m-%d")) as days, 
CASE WHEN datediff('2018-01-01', DATE_FORMAT(max(o_date),"%Y-%m-%d")) <= 30 THEN "3"
  WHEN datediff('2018-01-01', DATE_FORMAT(max(o_date),"%Y-%m-%d")) BETWEEN 31 AND 60 THEN "2"
     ELSE "1"
END AS R,
CASE WHEN COUNT(id_o) >= 5 THEN "3"
  WHEN COUNT(id_o) >= 2 AND COUNT(id_o) < 5 THEN "2"
     ELSE "1"
 END AS F,
t.M
FROM shop_1.orders_lessons_1 as o
join
(SELECT user_id,
CASE WHEN sum(price) >= 20000 THEN "3"
  WHEN sum(price) < 20000 and sum(price) >= 10000 THEN "2"
     ELSE "1"
END AS M   
FROM shop_1.orders_lessons_1 group by user_id) as t
on o.user_id = t.user_id
group by user_id
having R = "1") as s
on shop.user_id = s.user_id;

БЛОГ НА HUSL

RFM анализ. подсчет общего кол-ва пользователей и суммы

Ответы (1 шт):