如何在PostgreSQL中获取Based On Order By字段中从高到低的次高值记录 [英] How to get the second highest to lowest value record on the based on order by field in PostgreSQL
本文介绍了如何在PostgreSQL中获取Based On Order By字段中从高到低的次高值记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
JSON如下所示
{
"user": [
{
"user_name": "Devang",
"user_weight": 0.7676846955248864
},
{
"user_name": "Meet",
"user_weight": 1.1021
},
{
"user_name": "Devang",
"user_weight": 0.16163873153859706
},
{
"user_name": "Rajan",
"user_weight": 0.22163873153859706
}
],
"address": [
{
"address_name": "India"
}
]
}
我执行的查询是
WITH cte AS (
SELECT id, ('{user,'||index-1||'}')::text[] as json_path, value->'user_weight'
FROM user_table, jsonb_array_elements(json_field->'user')
WITH ordinality arr(value, index) WHERE arr.value->>'user_name' IN ('Devang', 'Meet') order by id, value->'user_weight' DESC
) select * from cte;
获取此类记录
id json_path ?column?
1 {user,1} 1.1021
1 {user,0} 0.7676846955248864
1 {user,2} 0.16163873153859706
2 {user,0} 0.7676846955248864
2 {user,1} 0.07447325861051013
我想要第二高到最低的记录,就像这样
id json_path ?column?
1 {user,1} 1.1021 # this is the highest one in 1 id so I do not need this
1 {user,0} 0.7676846955248864
1 {user,2} 0.16163873153859706
2 {user,0} 0.7676846955248864 # this is the highest one in 2 ids so. I do not want
2 {user,1} 0.07447325861051013
输出将为
id json_path ?column?
1 {user,0} 0.7676846955248864
1 {user,2} 0.16163873153859706
2 {user,1} 0.07447325861051013
浏览此处Demo
我们将感谢您的回复
推荐答案
有很多方法可以做到这一点,但如果您想使用窗口函数,您可以RANK()
或DENSE_RANK()
按id加权,并消除外部查询中的第一个排名:
WITH cte AS (
SELECT id, ('{user,'||index-1||'}')::text[] as json_path, (value->'user_weight')::text::numeric AS weight
FROM user_table, jsonb_array_elements(json_field->'user')
WITH ordinality arr(value, index)
WHERE arr.value->>'user_name' IN ('Devang', 'Meet')
ORDER BY id, value->'user_weight' DESC
)
SELECT * FROM (
SELECT cte.*,
RANK() OVER (PARTITION BY id ORDER BY id,weight DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r
FROM cte) j
WHERE r > 1;
演示:db<>fiddle
这篇关于如何在PostgreSQL中获取Based On Order By字段中从高到低的次高值记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文