postgresql 如何根据时间戳和分数分组排序

wecizke3  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(2)|浏览(238)

如何根据时间戳和分数分组排序
1.以前缀开头的实体(假设a1是类似的),但我们需要选择得分最高的实体,在本例中为a1-1
1.对于a2,它应该拾取a2-1,因为它的得分为0.6
1.对于a3,它应该是a3-0
1.对于a4,它应该是a4-1,因为它最近
1.假设时间是基于创建时间升序(即t1,t2.)
entities table包含以下内容

1. a1-0, x0, 0.8, t1
2. a2-0, x1, 0.5, t2
3. a2-1, x2, 0.6, t3
4. a3-0, x3, 0.8, t4
5. a2-2, x4, 0.7, t5
6. a1-1, x5, 0.9, t6
7. a4-0, x6, 0.6, t7
8. a4-1, x7, 0.6, t8
9. a1-2, x8, 0.85, t9

这是我期望的输出,我应该使用什么SQL查询

  1. A4-1
    1.公司简介
  2. A2-1
  3. A1-1
czq61nw1

czq61nw11#

来自doc:
SELECT DISTINCT ON ( expression [, ...] )只保留给定表达式计算结果等于的每组行中的第一行。DISTINCT ON表达式使用与ORDER BY相同的规则进行解释(见上文)。请注意,每个集合的“第一行”是不可预测的,除非使用ORDER BY来确保所需的行首先出现。
您可以将其与string functions合并结合起来,根据前缀建立集合。Demo

SELECT DISTINCT ON( (string_to_array(code,'-'))[1] )
       code
FROM entities
ORDER BY (string_to_array(code,'-'))[1] DESC, 
         score         DESC, 
         creation_time DESC;

| 代码|
| --|
| A4-1|
| 公司简介|
| a2-2 (得分为0.7,高于a2-1的0.6)|
| A1-1|

  • (string_to_array(code,'-'))在每个-处切割代码,生成一个数组,然后[1]获取其第一个元素,这将是前缀。
  • ORDER BY表明前缀主要负责位置。你想要最高前缀第一,所以你需要下降秩序。DISTINCT ON将为每个前缀丢弃除一条记录之外的所有记录。
  • 在共享前缀的每一组记录中,您想要最高的分数,因此第二个ORDER BY表达式是score DESC
  • 最新的时间戳将具有最高的值,因此要根据最近的创建时间选择a4-1而不是a4-0,您需要creation_time DESC

让你的结构正常化是件好事。您将多条信息保存为单个code,这迫使您使用字符串函数来分离它们并单独处理:

  • 如果这就是您接收数据的方式,您可以添加生成的列来自动解析代码,或者在表的顶部设置view
  • 如果您可以控制这些记录的构造,则将每个基础信息片段保存保存到单独的列中。以a2-0为例,将主要组标识符a放入一列,将次要组标识符2放入另一列,将订单号0放入另一列。您可能更喜欢只拆分为前缀和后缀,但如果您考虑到文本将a800排序为比a9demo)更低的值,那么额外的粒度可能是值得的。
oxf4rvwz

oxf4rvwz2#

User Postgresql's brilliant distinct on with custom order.

select distinct on (split_part(ent_id, '-', 1)) ent_id
from the_table
order by (split_part(ent_id, '-', 1)), score desc, ts;

| ent_id|
| --|
| A1-1|
| A2-2|
| 公司简介|
| A4-0|
请注意,“a2-2”的得分高于“a2- 1”。
现场演示here

相关问题