当前位置:网站首页>HQL find the maximum value in a range
HQL find the maximum value in a range
2022-04-23 12:57:00 【Look at the data at the top of the mountain】
The main thing to understand is
Strings can be sorted , But you can't do the best , For example, the date here
This problem is to find the largest value in the start time and end time , And it's also a way to turn two columns into one example
The answer to the final query
select user_id,
max(num) max_num
from (
select id,
user_id,
dt,
sum(p) over(partition by user_id order by dt) num
from (
select id,
user_id,
begin_date dt,
1 p
from test
union
select id,
user_id,
end_date dt,
-1 p
from test
) t1
) t2
group by user_id;
Test statement
create table if not exists test(
id int,
user_id string,
begin_date string,
end_date string
)
row format delimited
fields terminated by ',';
insert into test values(1,'A','2020-01-01','2020-01-30');
insert into test values(2,'A','2020-01-02','2020-01-30');
insert into test values(3,'A','2020-01-10','2020-02-10');
insert into test values(4,'B','2020-02-11','2020-02-30');
insert into test values(5,'C','2020-01-01','2020-01-30');
Let's decompose this table , Then merge , Get the fields we want
-- Which table to get the start time
select id,user_id,begin_date dt, 1 p
from test
1 A 2020-01-01 1
2 A 2020-01-02 1
3 A 2020-01-10 1
4 B 2020-02-11 1
5 C 2020-01-01 1
-- Which table to get the end time
select id,user_id,end_date dt, -1 p
from test
1 A 2020-01-30 -1
2 A 2020-01-30 -1
3 A 2020-02-10 -1
4 B 2020-02-30 -1
5 C 2020-01-30 -1
Merge the two tables (union)( Turn... Into two columns )
id user_id dt p
1 A 2020-01-01 1
1 A 2020-01-30 -1
2 A 2020-01-02 1
2 A 2020-01-30 -1
3 A 2020-01-10 1
3 A 2020-02-10 -1
4 B 2020-02-11 1
4 B 2020-02-30 -1
5 C 2020-01-01 1
5 C 2020-01-30 -1
Then group this table . And then to dt Sort , Also on p In sum
1 A 2020-01-01 1
2 A 2020-01-02 2
3 A 2020-01-10 3
2 A 2020-01-30 1
1 A 2020-01-30 1
3 A 2020-02-10 0
4 B 2020-02-11 1
4 B 2020-02-30 0
5 C 2020-01-01 1
5 C 2020-01-30 0
Finally, it's going on use_id Grouping , Go to the biggest p that will do
This result is the maximum value in a certain range , That is, the largest number of positions
版权声明
本文为[Look at the data at the top of the mountain]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230615025362.html
边栏推荐
- Byte jump 2020 autumn recruitment programming question: quickly find your own ranking according to the job number
- Process virtual address space partition
- Aviation core technology sharing | overview of safety characteristics of acm32 MCU
- Homomorphic encryption technology learning
- 教你快速开发一个 狼人杀微信小程序(附源码)
- Deploying MySQL in cloud native kubesphere
- Redis deployment of cloud native kubesphere
- Pytorch: a pit about the implementation of gradreverselayer
- SSM framework series - data source configuration day2-1
- MySQL supports IP access
猜你喜欢
【每日一题】棋盘问题
22. 括号生成
CGC: contractual graph clustering for community detection and tracking
mysql支持ip访问
STM32 project transplantation: transplantation between chip projects of different models: Ze to C8
解锁OpenHarmony技术日!年度盛会,即将揭幕!
Free and open source intelligent charging pile SaaS cloud platform of Internet of things
【蓝桥杯】4月17日省赛刷题训练(前3道题)
Mysql8 installation
Idea的src子文件下无法创建servlet
随机推荐
Free and open source charging pile Internet of things cloud platform
Unable to create servlet under SRC subfile of idea
mysql8安装
leetcode-791. Custom string sorting
Trier les principales utilisations de l'Agent IP réseau
Fashion cloud learning - input attribute summary
大家帮我看一下这是啥情况,MySQL5.5的。谢了
拥抱机器视觉新蓝海,冀为好望开启数字经济发展新“冀”遇
Labels and paths
What are the forms of attack and tampering on the home page of the website
Plato farm - a game of farm metauniverse with Plato as the goal
航芯技术分享 | ACM32 MCU安全特性概述
Date time type in database
Try the server for one month for free, and attach the tutorial
The continuous construction of the Internet industry platform is not only able to collect traffic
Use source insight to view and edit source code
Learning materials
Customize the shortcut options in El date picker, and dynamically set the disabled date
If you were a golang interviewer, what questions would you ask?
Byte jump 2020 autumn recruitment programming question: quickly find your own ranking according to the job number