SQL常见查询场景

实现SQL查询的几个常用场景
数据源：

CREATE TABLE `deal` (
  `uid` varchar(255) DEFAULT NULL,
  `time` varchar(255) DEFAULT NULL,
  `amount` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
INSERT INTO `deal` VALUES ('1', '2019-02-08', '6214');
INSERT INTO `deal` VALUES ('1', '2019-02-08', '8247');
INSERT INTO `deal` VALUES ('1', '2019-02-09', '850');
INSERT INTO `deal` VALUES ('1', '2019-02-10', '985');
INSERT INTO `deal` VALUES ('1', '2019-02-09', '285');
INSERT INTO `deal` VALUES ('1', '2019-02-11', '1285');
INSERT INTO `deal` VALUES ('2', '2019-02-14', '1037');
INSERT INTO `deal` VALUES ('2', '2019-02-15', '369');
INSERT INTO `deal` VALUES ('2', '2019-02-15', '795');
INSERT INTO `deal` VALUES ('2', '2019-02-19', '715');
INSERT INTO `deal` VALUES ('2', '2019-02-21', '537');
INSERT INTO `deal` VALUES ('2', '2019-02-24', '1037');
INSERT INTO `deal` VALUES ('3', '2019-02-09', '967');
INSERT INTO `deal` VALUES ('3', '2019-02-10', '850');
INSERT INTO `deal` VALUES ('3', '2019-02-10', '769');
INSERT INTO `deal` VALUES ('3', '2019-02-13', '940');
INSERT INTO `deal` VALUES ('3', '2019-02-19', '843');
INSERT INTO `deal` VALUES ('3', '2019-02-11', '850');
INSERT INTO `deal` VALUES ('3', '2019-02-14', '843');
INSERT INTO `deal` VALUES ('3', '2019-02-15', '900');

场景一：查询出每个uid交易额最大的时间和交易额，查询结果为（uid,time,amount）, 注意某一用户最大交易额可能有多次

SELECT
	a.uid,
	a.time,
	a.amount
FROM
	deal a
WHERE
	1 > ( #最大值就是和1比较，如果是取top3交易额，这里就用3，即排名为0/1/2的
		SELECT
			count(distinct amount) #注意这里不加distinct的区别，加上不会出现统计错误
		FROM
			deal
		WHERE
			a.amount < amount  #统计比当前值大的个数，如果当前值为最大值，则比它大的数为0
		AND a.uid = uid #这里限定在组内进行统计，如果去掉该条件，则为整体统计最大值
	)

方法2： 先查出每个id的最大amount

SELECT
	uid,
	time,
	amount
FROM
	deal
WHERE
	(uid, amount) IN (
		SELECT
			uid,
			max(amount) AS amount
		FROM
			deal
		GROUP BY
			uid
	)

方法3： 窗口函数 dense_rank() 或者rank()
注意：mysql中不支持

SELECT
	*
FROM
	(
		SELECT
			uid,
			time,
			amount,
			rank() over (
				PARTITION BY uid #按照uid进行分组
				ORDER BY
					amount DESC #根据amount字段值排序
			) AS rank
		FROM
			deal
	) a
WHERE
	rank = 1;

场景二：组内排序取前三
方法1：

SELECT
	a.uid,
	a.time,
	a.amount
FROM
	deal a
WHERE
	3 > (
		SELECT
			count(DISTINCT amount)
		FROM
			deal
		WHERE
			a.amount < amount
		AND a.uid = uid
	)
ORDER BY
	uid,
	amount DESC

方法2：利用窗口函数

SELECT
	*
FROM
	(
		SELECT
			uid,
			time,
			amount,
			rank() over (
				PARTITION BY uid #按照uid进行分组
				ORDER BY
					amount DESC #根据amount字段值排序
			) AS rank
		FROM
			deal
	) a
WHERE
	rank < 4;

场景三：统计每个用户每天最大交易额

SELECT
	uid,
	time,
	max(amount) AS max_amount
FROM
	deal
GROUP BY
	uid,
	time

场景四：统计每个用户每天的交易总额以及截止当天累计的交易额
方法1：

SELECT
	a.uid,
	a.time,
  	sum(a.amount) as day_amount, #每天的交易总额
	(
		SELECT
			sum(amount)
		FROM
			deal
		WHERE
			time <= a.time
		AND a.uid = uid
	) AS sum_amount
FROM
	deal a
GROUP BY
	a.uid,a.time
ORDER BY
	a.uid,
	a.time

方法2：使用窗口函数

SELECT
	uid,
	time,
	day_amount,
	sum(day_amount) over (PARTITION BY uid ORDER BY time) AS sum_amount
FROM
	(
		SELECT
			uid,
			time,
			sum(amount) AS day_amount
		FROM
			deal
		GROUP BY
			uid,
			time
	) a