【SQL 经典题】

构造分区

需求：每一次titlechinese变更，都重新排序；

--建表
CREATE TABLE test(
	titlechinese VARCHAR(100),
	changedate VARCHAR(10)
);

-- 插入数据
INSERT INTO test VALUES('地区销售经理','2019-12-13');
INSERT INTO test VALUES('地区销售经理','2017-10-10');
INSERT INTO test VALUES('地区销售经理','2017-01-09');
INSERT INTO test VALUES('地区销售经理','2016-12-09');
INSERT INTO test VALUES('地区销售经理','2016-12-06');
INSERT INTO test VALUES('高级医学信息专员(销售主管)','2015-01-13');
INSERT INTO test VALUES('地区销售经理','2014-01-21');
INSERT INTO test VALUES('地区销售经理','2014-01-20')；
INSERT INTO test VALUES('高级医学信息专员','2013-12-09');

（1）获取变更的第一条记录

SELECT
	*,
	IF(titlechinese != prevtitle,1,0) rk
FROM
(
	SELECT
		*,
		LAG(titlechinese,1,'null') OVER() prevtitle
	FROM  test
	ORDER BY changedate ASC
) t

(2) 现在需要按照 titlechinese + 一个额外字段 才能进行分组，这个额外字段每到1变一下，到0不变

SELECT
	*,
	SUM(rk) OVER(ORDER BY rn)
FROM
(
	SELECT
		*,
		IF(titlechinese != prevtitle,1,0) rk,
		ROW_NUMBER() OVER() rn
	FROM
	(
		SELECT
			*,
			LAG(titlechinese,1,'null') OVER() prevtitle
		FROM  test
		ORDER BY changedate ASC
	) t	
) t1

现在可以按照titlechinese + sum() 进行分组了！

（3）分组排序，完成需求

SELECT
	titlechinese,
	changedate,
	RANK() OVER(PARTITION BY titlechinese,groupid ORDER BY rn)
FROM
(
	SELECT
		titlechinese,changedate,
		SUM(rk) OVER(ORDER BY rn) groupid,
		rn
	FROM
	(
		SELECT
			titlechinese,changedate,
			IF(titlechinese != prevtitle,1,0) rk,
			ROW_NUMBER() OVER() rn
		FROM
		(
			SELECT
				titlechinese,changedate,
				LAG(titlechinese,1,'null') OVER() prevtitle
			FROM  test
			ORDER BY changedate ASC
		) t	
	) t1
) t2
ORDER BY changedate DESC