可能更好的SQL查询多个UNIONS-获取不同键名的最新5条记录 [英] Possible better SQL Query for multiple UNIONS - fetch latest 5 records for different `key` names

问题描述

我们有一个像这样的表

------------------------------------------
| user_id | timestamp | key      | value |
------------------------------------------
   99     | 1598603308 | Heart | 99
   99     | 1598603318 | Heart | 98
   99     | 1598603328 | Heart | 96
   99     | 1598603338 | Heart | 97
...
...
   99     | 1598603307 | Breath |      12
   99     | 1598603311 | Breath |      21
   99     | 1598603315 | Breath |      13
   99     | 1598603319 | Breath |      15

这个想法是要获得最后的5次呼吸ID为99的用户获得最后5颗心.

The idea is to get the last 5 Breath & last 5 Heart for the user with id 99.

(select user_id, max(t.timestamp), t.key, avg(t.value) from (
    (select user_id, key, timestamp, value from session_records
         where
     user_id = 99 and key = 'HeartRate' and value > 0 order by timestamp desc limit 5)
        union
    (select user_id, key, timestamp, value from session_records
        where
     user_id = 99 and key = 'BreathRate' and value > 0 order by timestamp desc limit 5)
        union
    (select user_id, key, timestamp, value from session_records
        where
     user_id = 99 and key = 'Sdnn' and value > 0 order by timestamp desc limit 5)
)

现在key可以改变.它可以是HeartRate或Sdnn或动态传入的内容.

Now the key can vary. It can be HeartRate or Sdnn or something dynamically passed in.

还有其他没有工会的方式来编写此查询吗?

Is there any other way to write this query without unions?

推荐答案

由于您似乎希望能够指定数量可变的参数，因此可以创建一个SQL函数，该函数采用值数组并将结果作为表返回.

Since it appears you want the ability to specify a variable number of parameters you can create a SQL function which takes an array of values and returns results as a table.

create or replace 
function user_latestn( user_id_in  integer
                     , latest_n_in integer 
                     , key_list_in text[]
                     ) 
  returns table(user_id      integer
               , "timestamp" bigint
               , key         text 
               , adv_value   numeric
               )
  language sql 
  strict
as $$
    select user_id,max("timestamp"), key, avg(value)
      from ( select user_id, key, "timestamp", value 
                  , row_number() over (partition by user_id, key 
                                           order by "timestamp" desc) rn
               from session_records
              where key = any (key_list_in)
                and user_id = user_id_in
                and value > 0
           ) t
       where rn <= latest_n_in
       group by user_id, key ;
$$;

然后可以选择结果.请参见示例此处.

由于您的数据仅包含4个项目，因此在示例中，我将数字减少为3(确保至少省略了1个).此外，您的数据与查询中的值也不匹配:Heart与HeartRate不匹配.我用了你的数据值.

You can then Select the results. See example here.

Since you data only included 4 items I reduced the number the number to 3 for the example (ensure at least 1 omitted). Also your data does not match the values in query: Heart does not match HeartRate. I used you data values.

这篇关于可能更好的SQL查询多个UNIONS-获取不同键名的最新5条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！