当前位置:网站首页>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
边栏推荐
- Servlet监听器&过滤器介绍
- Use compressorjs to compress pictures, optimize functions, and compress pictures in all formats
- 0基础可以考CPDA数据分析师证书吗
- CVPR 2022 & ntire 2022 | the first transformer for hyperspectral image reconstruction
- CVPR 2022&NTIRE 2022|首个用于高光谱图像重建的 Transformer
- [Blue Bridge Cup] April 17 provincial competition brushing training (the first three questions)
- Mysql8 installation
- Object.keys后key值数组乱序的问题
- CGC: contractual graph clustering for community detection and tracking
- Object. The disorder of key value array after keys
猜你喜欢
Date time type in database
世界读书日:我想推荐这几本书
Labels and paths
bert-base-chinese下载(智取)
Record a website for querying compatibility, string Replaceall() compatibility error
Recovering data with MySQL binlog
云原生KubeSphere部署Redis
Introduction to metalama 4 Use fabric to manipulate items or namespaces
Van uploader upload picture implementation process, using native input to upload pictures
Softbank vision fund entered the Web3 security industry and led a new round of investment of US $60 million in certik
随机推荐
Remote sensing image classification and recognition system based on convolutional neural network
PC starts multiple wechat at one time
Please help me see what this is, mysql5 5. Thanks
Ad20 supplementary note 3 - shortcut key + continuous update
Kubernetes 入门教程
mysql8安装
BUUCTF WEB [BUUCTF 2018]Online Tool
NPDP|产品经理如何做到不会被程序员排斥?
SSM framework series - annotation development day2-2
【csnote】ER图
实现一个盒子在父盒子中水平垂直居中的几种“姿势”
SSM framework series - data source configuration day2-1
Introduction to metalama 4 Use fabric to manipulate items or namespaces
Introduction to kubernetes
梳理网络IP代理的几大用途
If you were a golang interviewer, what questions would you ask?
Date time type in database
只是不断地建构平台,不断地收拢流量,并不能够做好产业互联网
How to click an object to play an animation
Free and open source charging pile Internet of things cloud platform