1

My goal - is to group my data by a specific field, and select also the latest value from another field on that group.

I have the following table:

| type | count | action | time                         |
   a       5      put     2021-04-21 15:13:02.104308224 
   b       7      put2    2021-04-20 15:13:02.104308224 
   b       1      get     2021-04-19 15:13:02.104308224 
   a       4      put6    2021-04-18 15:13:02.104308224 
   c       5      get     2021-04-17 15:13:02.104308224 
   a       6      put     2021-04-17 15:13:02.104308224

My statement is as follows:

 SELECT
    type,
    SUM(count) as total,
FROM mydb.mytable
WHERE time between ago(1h) and now()
GROUP type

I will eventually get the following rows:

| type | total
   a      15  
   b      8
   c      5

The thing is, I need to add to each row (that was grouped) also the action of the most recent row - so it should be

| type | total | action
   a      15      put
   b      8       put2
   c      5       get

how can it be achieved? Thanks!

ArielB
  • 1,184
  • 2
  • 11
  • 36

1 Answers1

2

if it supports window functions :

select 
   type,
   lastaction, 
   SUM(count) as total
  from (
    SELECT   *, 
    first_value(action) over (partition by type order by time desc) lastaction
    FROM mydb.mytable
    WHERE time between ago(1h) and now()
)
GROUP BY type, lastaction
ArielB
  • 1,184
  • 2
  • 11
  • 36
eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • I think it does! but i get the following error: line 7:39: ORDER BY expression 'time' must be an aggregate expression or appear in GROUP BY clause – ArielB Apr 21 '21 at 16:20
  • @ArielB how about now? – eshirvana Apr 21 '21 at 16:23
  • 1
    Nice! it works nicely :) Thanks for helping a guy that haven't touched SQL for ages :) – ArielB Apr 21 '21 at 16:34
  • One last question - is it possible to select 2 fields' first_value? without partitioning twice? (which sounds an overhead?) - my workaround is to concat - first_value(CONCAT(action,'#',anothercolumn)) – ArielB Apr 22 '21 at 10:22
  • @ArielB yes , if the window is the same , that should work – eshirvana Apr 22 '21 at 13:38
  • Timestream appears to not support multiple fields, and one more question - is it possible to filter the partition? let's say i have another field which can be "1" "2" or "3", i wanna choose the latest value only for the partitions with the value "2", even if 1 came later? – ArielB Apr 22 '21 at 14:26
  • @ArielB make a new question for that , with sample data and desired output – eshirvana Apr 22 '21 at 14:28