我給出了一個家庭作業來解釋以下查詢：
select
date,
country,
count(*) as `result`
from (
select
user_id,
event_date as date,
any_value(geo.country) as country
from `xxxx-android.events.all`
where event_date between current_date - interval 2 day and current_date - interval 1 day
group by user_id, date
)
group by date, country
order by date, count(*) desc
我嘗試使用以下代碼使用此在線編輯器在 MySql 中復制它：
CREATE TABLE events (
user_id INTEGER,
event_date DATE,
country TEXT -- NOT NULL
);
-- insert some values
INSERT INTO events VALUES (1, '2022-1-9', 'USA');
INSERT INTO events VALUES (1, '2022-1-9', 'USA');
INSERT INTO events VALUES (1, '2022-1-10', 'USA');
INSERT INTO events VALUES (2, '2022-1-9', 'UK');
INSERT INTO events VALUES (2, '2022-1-10', 'UK');
INSERT INTO events VALUES (2, '2022-1-9', 'UK');
INSERT INTO events VALUES (3, '2022-1-9', 'USA');
INSERT INTO events VALUES (3, '2022-1-10', 'USA');
INSERT INTO events VALUES (3, '2022-1-10', 'USA');
INSERT INTO events VALUES (4, '2022-1-9', 'AUT');
INSERT INTO events VALUES (4, '2022-1-10', 'AUT');
INSERT INTO events VALUES (5, '2022-1-10', 'AUT');
INSERT INTO events VALUES (5, '2022-1-10', 'AUT');
INSERT INTO events VALUES (6, '2022-1-9', NULL);
INSERT INTO events VALUES (6, '2022-1-9', NULL);
-- fetch some values
-- SELECT * FROM events;
-- select curdate() - interval 2 day;
-- select curdate() - interval 1 day;
select
event_date,
country,
count(*) as 'result'
from (
-- begin internal query
select
user_id as user_id,
event_date as event_date,
any_value(country) as country
from events
where event_date between curdate() - interval 2 day and curdate() - interval 1 day
group by user_id, event_date
-- end begin internal query
) as whatever
group by event_date, country
order by event_date, count(*) desc
我得到下表：
Output:
event_date country result
2022-01-09 USA 2
2022-01-09 UK 1
2022-01-09 AUT 1
2022-01-09 NULL 1
2022-01-10 USA 2
2022-01-10 AUT 2
2022-01-10 UK 1
如果用戶一天輸入多次，結果不應該計算嗎？例如，ID 為 1 的用戶在“2022-01-09”中輸入了 2 次，但在輸出中僅計為 1。
uj5u.com熱心網友回復：
由該術語在隱含Group BY在所述內SELECT
您按用戶 ID 和事件分組
group by user_id, event_date
但
user_id as user_id,
event_date as event_date,
any_value(country) as country
不會顯示任何重復的值，請參閱any_value 的手冊
select user_id as user_id, event_date as event_date, any_value(country) as country from events where event_date between curdate() - interval 2 day and curdate() - interval 1 day group by user_id, event_date用戶 ID | 事件日期 | 國家 ------: | :--------- | :------ 1 | 2022-01-09 | 美國 1 | 2022-01-10 | 美國 2 | 2022-01-09 | 英國 2 | 2022-01-10 | 英國 3 | 2022-01-09 | 美國 3 | 2022-01-10 | 美國 4 | 2022-01-09 | 奧特 4 | 2022-01-10 | 奧特 5 | 2022-01-10 | 奧特 6 | 2022-01-09 | 空值
db<>在這里擺弄
這就是為什么 09.09.2022 的計數只有 2 而不是 3
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/409072.html
標籤：
上一篇：如何通過laravel遷移在mysql中創建一個不可見的列？
下一篇：SQL查詢-我不知道所有識別符號