sql - 如何在 PostgreSQL 中基于 order by 字段获得第二高到最低值的记录
问题描述
在这里,我有一个 JSON 字段,我想在数据所在的每个 id 上选择第二高到最低的记录
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
看这里演示
任何答案将不胜感激
解决方案
有很多方法可以做到这一点,但如果您想使用窗口函数,您可以使用 idRANK()
或DENSE_RANK()
权重,并消除外部查询中的第一行:
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
推荐阅读
- docker - 通过 Apache Airflow 在 Python 子进程中运行 docker 命令的权限问题
- debugging - 默认 ASP.NET Core MVC 项目不会运行
- ruby-on-rails - 无法从 ckeditor gem 访问嵌套参数
- django - 如何使用 django 检查表中是否已存在数据
- r - 当 trimws 不起作用时如何修剪空白?
- javascript - 如何将 Angular 属性添加到以函数为参数的动态生成的元素?
- javascript - 从各个模块获取单独的导出
- vb.net - 如何将 MouseHover 和 MouseLeave 应用于两个控件以充当一个控件?
- php - 列出的项目未对齐并连续跳过 2 个项目空间
- react-native - 带有“./environment/validate.fx”的 Jest 中出现意外的字符串错误;