0
410
Viewspostgresql - locate random row inbetween two specific rows which follow a pattern
Scratching my head trying to get this to work. I have a historic table which has multiple rows (actions) for an account. I know the pattern for before and after a specific action and would like a query to locate the action inbetween for all accounts on that table. Trouble is everything I've tried can locate the actions I want to use as a guide (before and after) but not the one in the middle.
For instance, here is a table example to help explain the scenario:
|action id | comment | timestamp |
|----------|-----------------|--------------|
| 0110 | random comment | timestamp 1 |
| 0117 | text pattern 1 | timestamp 2 |
| 0129 | RANDOM COMMENT | timestamp 3 |
| 0130 | text pattern 2 | timestamp 4 |
| 0136 | random comment | timestamp 5 |
| etc.. | | |
So as you can see the only consistent pattern I have to work with is the text pattern before and after the target row (id 129). I included the timestamps as I thought perhaps it might be something that could perhaps be used? There are other columns on the table but they are essentially random and not much use for purposes of this query.
Any ideas on how I can achieve this?
Thanks in advance for any advice, it's very much appreciated.
Code I used which did not work:
select distinct ht.accountid, ht.id, ht.comment
from historic_table ht
where ht.id > (select MAX(ht1.id) from historic_table ht1 where ht1.accountid = ht.accountid and ht1.comment ilike '%comment pattern 1%')
and ht.id < (select MAX(ht2.id) from historic_table ht2 where ht2.accountid = ht.accountid and ht2.comment ilike '%comment pattern 2%')
limit 10
And here is an example of the output I'm looking to select. In yellow are the rows before and after with the distinct text patterns. In green I have highlighted all the rows I'd like to see in the output. I included only 2 examples one with 1 target row and another with multiple target rows. I hope this helps clarify:
2 answers
Answer question0
This not perfect, not optimal, but it appears to work. The CTE guarantees that the actionhistory table is only scanned once, but it will still be a sequential scan, because of the ILIKE '% zzz%' .
If the history is relatively stable, I would probably save the ids of the start/stop records in a (temp) table instead of a CTE.
NOTE: this solution assumes that the records are properly ordered (every start pattern has exactly one matching stop pattern, and that they do not nest)
CREATE TABLE zaction(
id INTEGER NOT NULL PRIMARY KEY
, zcomment text
, ztimestamp timestamp NOT NULL
);
INSERT INTO zaction(id, zcomment, ztimestamp) VALUES
( 0110, 'random comment', '2017-04-27 12:00:00' )
,( 0117, 'text pattern 1', '2017-04-27 12:10:00' )
,( 0129, 'RANDOM COMMENT', '2017-04-27 12:20:00' )
,( 0130, 'text pattern 2', '2017-04-27 12:30:00' )
,( 0136, 'random comment', '2017-04-27 12:40:00' )
--
,( 1110, 'random comment', '2017-04-27 12:00:00' )
,( 1117, 'text pattern 1', '2017-04-27 12:10:00' )
,( 1123, 'RANDOM COMMENT', '2017-04-27 12:20:00' )
,( 1129, 'RANDOM CONTENT', '2017-04-27 12:20:00' )
,( 1130, 'text pattern 2', '2017-04-27 12:30:00' )
,( 1136, 'random comment', '2017-04-27 12:40:00' )
--
;
VACUUM ANALYZE zaction;
-- SELECT * FROM zaction;
EXPLAIN
WITH z1 AS (
SELECT za.id
, CASE WHEN zcomment ilike '%text pattern 1' THEN 1
WHEN zcomment ilike '%text pattern 2' THEN -1
ELSE 0 END AS dir
FROM zaction za
)
, z2 AS (
SELECT z1.id, z1.dir
, SUM(z1.dir) OVER (ORDER BY z1.id) AS yesno
FROM z1
)
SELECT za.*
FROM zaction za
JOIN z2 ON z2.id = za.id AND z2.yesno > 0 AND z2.dir = 0
;
Slightly modified version, which attempts to keep the CTE(s) small:
-- EXPLAIN
WITH z1 AS (
SELECT za.id
, CASE WHEN zcomment ilike '%text pattern 1' THEN 1
WHEN zcomment ilike '%text pattern 2' THEN -1
ELSE 0 END AS dir
FROM zaction za
WHERE zcomment ilike '%text pattern %' -- <<== preselection to keep the CTE small
)
SELECT za.*
FROM zaction za
JOIN ( -- <<== Join with subquery to give the optimiser some freedom
SELECT za.id, z1.dir
, SUM(z1.dir) OVER (ORDER BY za.id) AS yesno
FROM zaction za
LEFT JOIN z1 ON z1.id = za.id -- <<== remerge with original set, whicha coulduse an index
) z2 ON z2.id = za.id AND z2.yesno > 0 AND z2.dir IS DISTINCT FROM 1
;
0
I managed to get the results needed, albeit with a compromised output format by using string_agg to include all action ids and comments inbetween the two specified text patterns for all accounts. Posted it below just in case anybody else could make use of it:
select ht.accountid
,string_agg(ht.id::text, ', ') as actions_ids
,string_agg(ht.comment, ', ') as comments
from historic_table ht
where ht.timestamp >= '2017-02-01'
and ht.timestamp < '2017-03-01'
and ht.id > (select max(ht1.id) from historic_table ht1 where ht1.accountid = ht.accountid and ht1.comment ilike '%comment pattern 1%')
and ht.id < (select max(ht2.id) from historic_table ht2 where ht2.accountid = ht.accountid and ht2.comment ilike '%comment pattern 2%')
group by ht.accountid
order by 1
Specifically I had to remove the distinct (which I didn't need anyway) and this was the culprit for causing the crash when I had run it before (the table was so large with many custom comments the distinct was trying to sort/unique the entire result set and overloaded the data stored in pg_temp). By adding a timestamp filter and dropping the distinct it actually runs pretty fast now.