MySQL работа с JOIN

Question

Немного не разобрался как строить таблицы более компактно, сорян)

В общем... и целом, задание, из таблицы deals выбрать кол-во строк по датам и по пользователям, в которых участвует obj или bid пользователя. Если в строке и obj и bid одного пользователя, то это 1(это в идеале, но можно и без этого).

Решить данную задачу я бы хотел сам, но нуждаюсь в подсказках, может есть какие статьи или вы можете, что-то подсказать?

Таблицы: Deals

id	obj	bid	date
1	1	2	2021-10-05
2	2	5	2021-10-05
3	3	4	2021-10-06
4	5	6	2021-10-06

obj

id	user_id
1	5
2	5
3	6
4	7
5	8

bid

id	user_id
1	7
2	6
3	5
4	5
5	8
6	8

Результат:

created	user_id	total
2021-10-05	5	2
2021-10-05	6	1
2021-10-05	8	1
2021-10-06	5	1
2021-10-06	6	1
2021-10-06	8	1

По вариантам, пока я додумался только до такого:

SELECT DATE(deals.date) as created, obj.user_id as user_id, count(deals.obj) as total 
FROM `deals` 
INNER JOIN obj ON obj.id = deals.obj
WHERE obj.user_id in (5,7) and DATE(deals.date) BETWEEN '2021-10-05' and '2021-10-06' 
GROUP BY obj.user_id, DATE(deals.date)

И такой же для таблицы bid, а затем уже в коде считать... Может есть какие, подобные примеры где это выполнено одним запросом?

MySQL 5.7

Answer 1

Может я и растянул количество операций, но просто шёл по этапам.

Этапы:

ALL - аналог таблицы Deals, только сразу с user_id;
DIFF - сравнение пользователей;
UNION - разбитие таблицы на разные столбцы и объединение.
Вывод итоговой таблицы с подсчётом total

Запрос:

WITH ALL_CTE (date, obj_user_id, bid_user_id)
AS
(
SELECT
   D.date,
   O.user_id AS obj_user_id,
   B.user_id AS bid_user_id
FROM Deals D
JOIN obj O ON O.id = D.obj
JOIN bid B ON B.id = D.bid
),
DIFF_CTE (date, obj_user_id, bid_user_id, diff)
AS
(
SELECT
   date,
   obj_user_id,
   bid_user_id,
   CASE
      WHEN obj_user_id = bid_user_id
      THEN 1
      ELSE 0
    END AS diff
FROM ALL_CTE
),
UNION_CTE (date, user_id, diff)
AS
(
SELECT
   date,
   obj_user_id AS user_id,
   diff
FROM DIFF_CTE
UNION ALL
SELECT
   date,
   bid_user_id AS user_id,
   diff
FROM DIFF_CTE
)
SELECT
   date AS created,
   user_id,
   CASE
      WHEN diff <> 1
      THEN COUNT(user_id)
      ELSE 1
   END AS total
FROM UNION_CTE
GROUP BY
   date,
   user_id,
   diff

Результат:

UPDATE:
MySQL 5.7

SELECT 
   date  AS created,
   user_id,
   CASE
      WHEN diff <> 1
      THEN COUNT(user_id)
      ELSE 1
   END AS total
FROM
(SELECT date, user_id, diff FROM
(SELECT
   D.date,
   O.user_id AS user_id,
   CASE
      WHEN O.user_id = B.user_id
      THEN 1
      ELSE 0
   END AS diff
FROM Deals D
JOIN obj O ON O.id = D.obj
JOIN bid B ON B.id = D.bid) t_diff
UNION ALL
SELECT date, user_id, diff FROM
(SELECT
   D.date,
   B.user_id AS user_id,
   CASE
      WHEN O.user_id = B.user_id
      THEN 1
      ELSE 0
   END AS diff
FROM Deals D
JOIN obj O ON O.id = D.obj
JOIN bid B ON B.id = D.bid) t_diff) t_union
GROUP BY
   date,
   user_id,
   diff

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=60118e2aba59369976e5b01726524df2

БЛОГ НА HUSL

MySQL работа с JOIN

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