mysql - 使用时间戳编写 SQL

Question

数据

CREATE TABLE IF NOT EXISTS `transactions` (
  `transactions_ts` timestamp ,
  `user_id` int(6) unsigned NOT NULL,
  `transaction_id` bigint, 
  `item` varchar(200), PRIMARY KEY(`transaction_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `transactions` (`transactions_ts`, `user_id`, `transaction_id`,`item` ) VALUES
  ('2016-06-18 13:46:51.0', 13811335,1322361417, 'glove'),
  ('2016-06-18 17:29:25.0', 13811335,3729362318, 'hat'), 
  ('2016-06-18 23::07:12.0', 13811335,1322363995,'vase' ),
  ('2016-06-19 07:14:56.0',13811335,7482365143, 'cup'),
  ('2016-06-19 21:59:40.0',13811335,1322369619,'mirror' ),
  ('2016-06-17 12:39:46.0',3378024101,9322351612, 'dress'),
  ('2016-06-17 20:22:17.0',3378024101,9322353031,'vase' ),
  ('2016-06-20 11:29:02.0',3378024101,6928364072,'tie'),
  ('2016-06-20 18:59:48.0',13811335,1322375547, 'mirror');

问题:对于每个用户，显示他们订购的第一件商品(按时间排在第一位)。我假设时间是一个完整的时间戳(不是单独的时间和日期)。我的尝试

select 
min(transactions_ts) as first_trans, 
user_id, item
from transactions
group by user_id
order by first_trans;

很抱歉，这可能是一个简单的问题，但有人告诉我，我的查询完全错误。而且我没有其他方法可以检验他的这种说法

demo fiddle

Answer 1

这比您想象的要复杂一点。

首先:“for each user”将转换为 GROUP BY user_id，而不是 GROUP BY user_id, item。

但是对于 GROUP BY user_id，您需要一个聚合函数，表示“item 用于最小 transactions_ts”。 MySQL 没有这种聚合功能。

显而易见的解决方案是执行以下两个步骤:

1. 找到每个用户的第一笔交易
2. 显示这些交易的项目

查询:

select *
from transactions
where (user_id, transactions_ts) in
(
  select user_id, min(transactions_ts)
  from transactions
  group by user_id
);

任务的另一种表达方式是:“给我不存在同一用户的旧交易的交易”。

查询:

select *
from transactions t
where not exists
(
  select *
  from transactions t2
  where t2.user_id = t.user_id
  and t2.transactions_ts < t.transactions_ts
);