Подсчет из нескольких таблиц с группой
У меня две таблицы. В первых таблицах регистрируются поступления. У второго есть выходы, как показано ниже:
Table 1: Admissions
+----------+---------------------+---------+
| entry_id | join_date | name |
+----------+---------------------+---------+
| 26 | 2017-01-01 00:00:00 | James |
| 29 | 2017-01-01 00:00:00 | Jan |
| 27 | 2017-01-01 00:00:00 | Chris |
| 28 | 2017-01-01 00:00:00 | Mary |
| 22 | 2017-01-02 00:00:00 | Anna |
| 21 | 2017-01-02 00:00:00 | Andy |
| 24 | 2017-01-02 00:00:00 | Bob |
| 20 | 2017-01-04 00:00:00 | Alice |
| 23 | 2017-01-04 00:00:00 | Chris |
| 25 | 2017-01-04 00:00:00 | Happy |
+----------+---------------------+---------+
Table 2: Exits
+----------+---------------------+----------+
| entry_id | exit_date | name |
+----------+---------------------+----------+
| 322 | 2017-01-01 00:00:00 | Kay |
| 344 | 2017-01-01 00:00:00 | Agnes |
| 920 | 2017-01-02 00:00:00 | Andre |
| 728 | 2017-01-02 00:00:00 | Mark |
| 583 | 2017-01-03 00:00:00 | Alsta |
| 726 | 2017-01-03 00:00:00 | Bull |
| 816 | 2017-01-03 00:00:00 | Jane |
| 274 | 2017-01-04 00:00:00 | Jack |
| 723 | 2017-01-04 00:00:00 | Anna |
| 716 | 2017-01-04 00:00:00 | Bill |
+----------+---------------------+----------+
Я ищу решение, чтобы узнать количество входов, количество выходов и баланс, сгруппированные по дате.
Я ищу это>
+---------------------+--------+--------+-----------+
| date | joins | exist | net |
+---------------------+--------+--------+-----------+
| 2017-01-01 00:00:00 | 4 | 2 | 2 |
| 2017-01-02 00:00:00 | 3 | 2 | 1 |
| 2017-01-03 00:00:00 | 0 | 3 | -3 |
| 2017-01-04 00:00:00 | 3 | 3 | 0 |
+---------------------+--------+--------+-----------+
Примечания: могут быть дни, когда происходит прием, но выходы не регистрируются, и наоборот.
Ответы 3
Ну вот:
SELECT
d,
SUM(CASE WHEN t = 'j' THEN 1 ELSE 0 END) as joins,
SUM(CASE WHEN t = 'x' THEN 1 ELSE 0 END) as exits,
SUM(CASE WHEN t = 'j' THEN 1 ELSE 0 END) - SUM(CASE WHEN t = 'x' THEN 1 ELSE 0 END) as net
FROM
(SELECT join_date as d, 'j' as t FROM admissions) j
UNION ALL
(SELECT exit_date as d, 'x' as t FROM exits) x
GROUP BY d
Мы объединяем данные, используя UNION ALL, и отмечаем их тип - соединение или выход с помощью простого символа, который мы можем сравнить позже.
Мы группируем это по d, давая по одной дате в строке, и суммируем результат условного просмотра того, является ли это 'j'oin или e'x'it. Если строка j, то в столбец, отслеживающий общее количество объединений за этот день, добавляется 1 и т. д.
Единственное, чего это вам не дает, - это дни, когда нет присоединений или выходов .. (Например, 2018-12-25, 0, 0, 0, потому что рождественский день был закрыт, и никто ничего не сделал в этот день) .. Но вы не сказали, что вам это нужно.
Если вам нужны строки с датой и 0 выходов, 0 присоединений, 0 сетей, тогда нам придется поработать некоторую дополнительную магию, и это немного больше головной боли / затрудняет понимание (поэтому я оставил это вне)
Следующее будет делать:
select
CASE WHEN join_date is not null THEN join_date
WHEN exit_date is not null THEN exit_date END as date,
entry.cnt as joins,
exit.cnt as exits,
(extry.cnt - exit.cnt) as net
FROM
(select join_date, COALESCE(count(*), 0) as cnt from Admissions group by join_date) entry
FULL OUTER JOIN
(select exit_date, COALESCE(count(*), 0) as cnt from Exits group by exit_date) exit
ON
entry.join_date=exit.exit_date
;
Несколько небольших советов для вас, Агравал: SELECT CASE WHEN можно более точно записать как SELECT COALESCE(join_date, exit_date). Нет необходимости COALESCE счетчик - он никогда не может вернуть нуль
Я не нашел ответа. Вот ответ одного из моих друзей, ниже версия MySQL:
select aa.date, IFNULL(aa.joins, 0) joins, IFNULL(bb.exits,0) exits, (IFNULL(aa.joins,0) - IFNULL(bb.exits,0)) net
from
(
select join_date date, count(name) joins
from Admissions
group by join_date
) aa
left join
(
select exit_date date, count(name) exits
from Exits
group by exit_date
) bb on aa.date = bb.date
UNION
select bb.date, IFNULL(aa.joins, 0) joins, IFNULL(bb.exits,0) exits, (IFNULL(aa.joins,0) - IFNULL(bb.exits,0)) net
from
(
select join_date date, count(name) joins
from Admissions
group by join_date
) aa
right join
(
select exit_date date, count(name) exits
from Exits
group by exit_date
) bb on aa.date = bb.date order by date;
Ваши друзья должны узнать, что такое ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Им также следует приложить некоторые усилия для улучшения своей производительности; этот запрос выполняет примерно в 3 раза больше работы, чем необходимо
@CaiusJard, я пытался оптимизировать ваши комментарии, но mysql не поддерживает ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, и в mysql работа выполняется так же, как stackoverflow.com/questions/4796872/…. так что решение специфично для mysql.
Прошу прощения; я забыл, что mysql все еще не поддерживает FOJ
Если ответ окажется полезным или полезным, не стесняйтесь нажимать стрелку вверх, чтобы проголосовать за него. Можно проголосовать за любое количество ответов, но только один можно отметить как принятый.