Re SQL Test Performance

[Proteusiq]

— get latest value

select distinct on (tag_id) tag_id, timestamp, some_data 
from blinks 
order by id, timestamp desc;

[Proteusiq]

— make this CTE
select id, date, another_info
from (
  select id, date, another_info, 
         row_number() over (partition by id order by date desc) as rn
  from the_table
) t
where rn = 1
order by id;

with blink_last_timestamp as (
     select tag_id, max(timestamp) as max_timestamp
     from blinks 
     group by tag_id )
 select bl.tag_id, max_timestamp, some_data
 from blink_last_timestamp bl 
 join blinks b on 
     b.tag_id = bl.tag_id and 
     bd.timestamp = bl.max_timestamp

[Proteusiq]

— date has to unique, so perhaps timestampe

select * 
from bar 
where (id,date) in (select id,max(date) from bar group by id)

[Proteusiq]

SELECT t1.*
   FROM yourTable t1
     LEFT JOIN yourTable t2 ON t2.tag_id = t1.tag_id AND t2.value_time > t1.value_time
  WHERE t2.tag_id IS NULL

[Proteusiq]

SELECT t.*
FROM
    (SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY usr_id ORDER BY time_stamp DESC) as r
    FROM lives) as t
WHERE t.r = 1

[Proteusiq]

SELECT * 
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
    SELECT usr_id, time_stamp, trans_id
    FROM lives sq
    WHERE sq.usr_id = outer.usr_id
    ORDER BY trans_id, time_stamp
    LIMIT 1
)

[Proteusiq]

-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
  last_value(time_stamp) OVER wnd,
  last_value(lives_remaining) OVER wnd,
  usr_id,
  last_value(trans_id) OVER wnd
 FROM lives
 WINDOW wnd AS (
   PARTITION BY usr_id ORDER BY time_stamp, trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 );

[Proteusiq]

Read: Last Value

[Proteusiq]

—avoid zero division 
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

[Proteusiq]

?

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
    FROM states;

[Proteusiq]

See https://www.postgresql.org/docs/15/functions-string.html

[Proteusiq]

date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00