unable to use the results of conditional_true

My table is

         ts          | symbol | bid
--------------------- -------- ------
 2009-01-01 03:00:00 | XYZ    |   10
 2009-01-01 03:00:03 | XYZ    |   11
 2009-01-01 03:00:06 | XYZ    | 10.5
 2009-01-01 03:00:09 | XYZ    |   11
 2009-01-01 03:00:00 | ABC    |   10
 2009-01-01 03:00:03 | ABC    |   11
 2009-01-01 03:00:06 | ABC    |   11
 2009-01-01 03:00:09 | ABC    |   11

I want to run a query in which I first get all rows with symbol ABC, then I want to put them in windows based on a condition and then select the 2nd window.

The subquery works (if not used a subquery)

select * 
from 
    (select 
         ts, symbol, bid, 
         conditional_true_event(bid > 10.6) over(order by ts) as window 
     from Tickstore3 
     where symbol = 'ABC')  as t3;

Output:

         ts          | symbol | bid | window
--------------------- -------- ----- --------
 2009-01-01 03:00:00 | ABC    |  10 |      0
 2009-01-01 03:00:03 | ABC    |  11 |      1 <-- I want to get only this row
 2009-01-01 03:00:06 | ABC    |  11 |      2
 2009-01-01 03:00:09 | ABC    |  11 |      3

But I am not able to get only the 2nd row

select * 
from 
    (select 
         ts, symbol, bid, 
         conditional_true_event(bid > 10.6) over(order by ts) as window 
     from Tickstore3 
     where symbol = 'ABC') as t3 
where window = 1;

I'm getting an error:

ERROR 4856: Syntax error at or near "window" at character 151
LINE 1: ...w from Tickstore3 where symbol='ABC') as t3 where window=1;

CodePudding user response：

found the mistake. I can't refer window. I have to use it with the temp result name

select * from (select ts, symbol, bid, conditional_true_event(bid > 10.6) over(order by ts) as
 window from Tickstore3 where symbol='ABC') as t3 where t3.window=1 ; <-- note t3.window
         ts          | symbol | bid | window
--------------------- -------- ----- --------
 2009-01-01 03:00:03 | ABC    |  11 |      1
(1 row)

CodePudding user response：

I think that CONDITIONAL_TRUE_EVENT() is a complete overkill here.

A conditional running sum (using a CASE WHEN ... expression) will do very nicely.

And, just for the fun of it, I add the named window clause, to demonstrate why it's a reserved word - and I think counter is a more appropriate name for the thing you want to filter by ...

The right programmer's words can make any documentation or comment superfluous if you choose wisely ...

WITH                                                                                                                                                                                                    
-- your input ...
tickstore(ts,symbol,bid) AS (
          SELECT TIMESTAMP '2009-01-01 03:00:00','XYZ',10
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:03','XYZ',11
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:06','XYZ',10.5
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:09','XYZ',11
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:00','ABC',10
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:03','ABC',11
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:06','ABC',11
UNION ALL SELECT TIMESTAMP '2009-01-01 03:00:09','ABC',11
)
-- real query starts here, replace following comma with "WITH" ...
,
w_counter AS (
  SELECT
    *
  , SUM(CASE WHEN bid > 10.6 THEN 1 END) OVER w AS counter
  FROM tickstore
  WHERE symbol='ABC'
  WINDOW w AS (ORDER BY ts)
)
SELECT
  ts
, symbol
, bid
, counter
FROM w_counter
WHERE counter=1
;
-- out          ts          | symbol | bid  | counter 
-- out --------------------- -------- ------ ---------
-- out  2009-01-01 03:00:03 | ABC    | 11.0 |       1