Разница между суммой (1) и суммой (количество *)) в sql?
У меня есть таблица со значениями:
CREATE TABLE IF NOT EXISTS `games` (
`date` DATE,
`item_id` varchar(40),
`player_id` varchar(40)
);
INSERT INTO `games` (`date`, `item_id`, `player_id`) VALUES
('2023-01-03', 'raven', 'david'),
('2023-01-04', 'folly', 'david'),
('2023-01-05', 'syd', 'david'),
('2023-01-05', 'syd', 'ire'),
('2023-01-01', 'raven', 'jane'),
('2023-01-03', 'syd', 'jane'),
('2023-01-03', 'folly', 'harry'),
('2023-01-10', 'syd', 'harry'),
('2023-01-10', 'syd', 'yvette')
;
Однако эти два запроса возвращают разные результаты:
SELECT
g.date,
g.item_id,
COUNT(*) AS item_games,
SUM(1) OVER (PARTITION BY g.date) AS total_matches
FROM games g
GROUP BY g.date, g.item_id
ORDER BY g.date, g.item_id;
возвращает:
2023-01-01 raven 1 1
2023-01-03 folly 1 3
2023-01-03 raven 1 3
2023-01-03 syd 1 3
2023-01-04 folly 1 1
2023-01-05 syd 2 1
2023-01-10 syd 2 1
пока
SELECT
g.date,
g.item_id,
COUNT(*) AS item_games,
SUM(count(*)) OVER (PARTITION BY g.date) AS item_game_ratio
FROM games g
GROUP BY g.date, g.item_id
ORDER BY g.date, g.item_id;
возвращает:
2023-01-01 raven 1 1
2023-01-03 folly 1 3
2023-01-03 raven 1 3
2023-01-03 syd 1 3
2023-01-04 folly 1 1
2023-01-05 syd 2 2
2023-01-10 syd 2 2
Я не понимаю, почему они возвращают разные значения только для последних двух строк.
Ответы 3
Мне кажется, что первый запрос говорит вам, сколько предметов используется в эту дату. Второй запрос сообщает вам, сколько игроков используют предмет в этот день.
Они возвращают разные значения для двух последних маршрутов, поскольку в каждый из этих дней у syd есть две игры.
Оконная функция выполняется после предложения GROUP BY. Логический порядок выполнения SQL-запроса следующий:
FROM and JOINs
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
ORDER BY
LIMIT / OFFSET
В вашем запросе сначала применяется предложение GROUP BY, а затем оконная функция.
(SUM(1) OVER (PARTITION BY g.date))здесь вы считаете строки группы по результату за каждую дату.- Однако
count(*)выполняется как часть GROUP BY, поэтому дляSUM(count(*)) OVER (PARTITION BY g.date)суммируемыми данными будет количество исходных строк для каждой даты.
SUM(1) это то, что вы редко увидите, потому что добавление 1 для каждой строки — это просто запутанный способ подсчета строк.
SUM(1) OVER (PARTITION BY g.date)
или
COUNT(*) OVER (PARTITION BY g.date)
подсчитайте, сколько строк существует для даты.
SUM(COUNT(*)) OVER (PARTITION BY g.date)
с другой стороны, суммируются все отдельные значения для каждой даты. Дата 05.01.2023 (и 10.01.2023, если уж на то пошло) имеет одну строку результата с COUNT(*) = 2. Построение суммы по-прежнему дает вам 2, потому что с одной строкой и одним значением складывать нечего.
Поскольку ваши результаты показывают одну строку для каждой даты и элемента, SUM(1) OVER (PARTITION BY g.date) дает вам количество различных элементов на дату, а SUM(COUNT(*)) OVER (PARTITION BY g.date) дает вам количество игр на дату.
Если это просто "SUM(1) AS total_matches" без оконной функции, то результаты будут такими же