1. 一. Problem Review
1. 1. Sub Query
1. 1.1 select * from ( sub query ) where ...
2. 1.2 with as
2. 2. Window Function
2. 二. Window Function Basic
1. 1. What is it?
2. 2. DDL & INSERT
3. 3. Sense
1. 3.1 The different between GROUP & WINDOW FUNCTION
2. 3.2 Basic usage
3. 3.3 The different in 4 ranking functions & fist/last/nth
4. 3.4 Transact-SQL in Window function
4. 4. friends links

一. Problem Review

业务需求: 当设备变更提前提醒时间时, 需要操作推送消息获取

1. 当天的
2. 推送时间最新的(update_time)
3. 推送结果(push_result)不是ok或不为NULL

的消息.

1. Sub Query

1.1 select * from ( sub query ) where ...

-- 子查询 select 嵌套
select
	filter_message_push.*
from
	(
	select
		tmp.*
	from
		t_message_push tmp
	join t_schedule_info tsi on
		tsi.id = tmp.service_id
	where
		tmp.device_id in ('926dcff784790582')
		and tmp.type = '1'
		and tmp.push_type = 'task'
		-- and tmp.push_task_time >= current_timestamp
		and ( tmp.push_result != 'ok'
			or tmp.push_result is null )
		and tsi.delete_flag = '0') as filter_message_push,
	(
	select
		tmp.service_id,
		max(tmp.update_time) as update_time
	from
		t_message_push tmp
	join t_schedule_info tsi on
		tsi.id = tmp.service_id
	where
		tmp.device_id in ('926dcff784790582')
			and tmp.type = '1'
			and tmp.push_type = 'task'
			-- and tmp.push_task_time >= current_timestamp
			and ( tmp.push_result != 'ok'
				or tmp.push_result is null )
			and tsi.delete_flag = '0'
		group by
			tmp.service_id) as grouped_message_push
where
	grouped_message_push.service_id		 = filter_message_push.service_id
	and grouped_message_push.update_time = filter_message_push.update_time;

1.2 with as

-- 子查询 with 语句
with filter_message_push as
    (
    select
		tmp.*
	from
		t_message_push tmp
	join t_schedule_info tsi on
		tsi.id = tmp.service_id
	where
		tmp.device_id in ('926dcff784790582')
		and tmp.type = '1'
		and tmp.push_type = 'task'
		-- and tmp.push_task_time >= current_timestamp
		and ( tmp.push_result != 'ok'
			or tmp.push_result is null )
		and tsi.delete_flag = '0'),

    grouped_message_push as
    (
    select
        service_id,
        max(update_time) as update_time
    from
        filter_message_push
    group by
        service_id )
select
	*
from
	filter_message_push join grouped_message_push
    on grouped_message_push.service_id 	 = filter_message_push.service_id
	and grouped_message_push.update_time = filter_message_push.update_time;

2. Window Function

-- 窗口函数
select
	*
from
	( -- 构造一个仅仅含有 id & rank 辅助表
	select
		tmp.id,
		rank() over (partition by tmp.service_id
					 order by     tmp.update_time DESC) as rank -- 我的改动 DESC ???
	from
		t_message_push tmp join t_schedule_info tsi
		on tsi.id = tmp.service_id
	where
		tmp.device_id in ('926dcff784790582')
		and tmp.type = '1'
		and tmp.push_type = 'task'
		-- and tmp.push_task_time >= current_timestamp
		and ( tmp.push_result != 'ok'
			  or tmp.push_result is null )
		and tsi.delete_flag = '0')
where
	rank = 1 ;

二. Window Function Basic

1. What is it?

窗口函数 相当于 不改变行数分组.

grammar:

<专有窗口函数 or 聚合函数> over(partition by <用于分组的列名> -- OPTIONAL
							order by 	 <用于排序的列名>)

2. DDL & INSERT

-- feature.`table` definition
CREATE TABLE `table` (
  `no` int NOT NULL,
  `class` int DEFAULT NULL,
  `grade` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO feature.`table` (`no`, class, grade) VALUES(1, 1, 86);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(2, 1, 95);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(3, 2, 89);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(4, 1, 83);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(5, 2, 86);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(6, 3, 92);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(7, 3, 86);
INSERT INTO feature.`table` (`no`, class, grade) VALUES(8, 1, 88);

select * from feature.`table`;

3. Sense

只要是有与排名相关的业务, 皆可用.

eg, 每个部门中显示器屏幕字体 最小的人 和 最大的人 的 显示字号之差.

3.1 The different between GROUP & WINDOW FUNCTION

select
	class,
	count(no)
from
	feature.`table` t
group by
	class;

select
	t.class,
	count(no) over( partition by class
					order by     class) as current_count
from
	feature.`table` t ;

3.2 Basic usage

select
	*,
	rank() over (partition by class
order by
	grade desc) as ranking
from
	feature.`table` t ;

3.3 The different in 4 ranking functions & fist/last/nth

select *,
   rank()		over (order by grade desc) as rank_fun,
   dense_rank() over (order by grade desc) as dense_rank_fun,
   row_number() over (order by grade desc) as row_number_fun,
   ntile(3)		over (order by grade desc) as ntile_fun -- 参数平分几组
from feature.`table` t 

select *,
   first_value(grade)	over (partition by class order by grade desc) as first_value_fun,
   last_value(grade)	over (partition by class order by grade desc) as last_value_fun,
   nth_value(grade, 2)	over (partition by class order by grade desc) as nth_value_fun
from feature.`table` t 

3.4 Transact-SQL in Window function

select *,
   sum(grade)   over (order by no) as current_sum,
   avg(grade)   over (order by no) as current_avg,
   count(grade) over (order by no) as current_count,
   max(grade)   over (order by no) as current_max,
   min(grade)   over (order by no) as current_min
from feature.`table` t ;

4. friends links

1. https://www.developerastrid.com/sql/sql-olap/

2. https://www.cnblogs.com/gjc592/p/13194119.html

3. https://blog.csdn.net/godlovedaniel/article/details/116571187