Get errors by left join and generating time series

0
votes

I want to fill all the interval by getting the error

enter image description here

Original aggregated by second query (it works)

    SELECT 
      date_trunc('second', ticktime) AS ticktime ,
      max(last_price) OVER w AS high ,
      min(last_price) OVER w AS low 
   FROM czces
   WHERE product_type ='TA' AND contract_month = '2014-08-01 00:00:00'::TIMESTAMP 
     WINDOW w AS (
      PARTITION BY date_trunc('second', ticktime)
      ORDER BY ticktime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      )

ERROR: JOIN/USING types bigint and timestamp without time zone cannot be matched

SELECT 
       t.high,
       t.low
FROM 
(

  SELECT generate_series(
    date_trunc('second', min(ticktime)) ,
    date_trunc('second', max(ticktime)) ,
    interval '1 sec'
  ) FROM czces  AS g (time)

  LEFT JOIN
  (
    SELECT 
      date_trunc('second', ticktime) AS time ,
      max(last_price) OVER w AS high ,
      min(last_price) OVER w AS low 
   FROM czces
   WHERE product_type ='TA' AND contract_month = '2014-08-01 00:00:00'::TIMESTAMP 
     WINDOW w AS (
      PARTITION BY date_trunc('second', ticktime)
      ORDER BY ticktime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      )
  ) t USING (time)



  ORDER BY 1 
) AS t ;

ERROR: subquery in FROM must have an alias LINE 5: (

    SELECT 
           t.high,
           t.low
    FROM 
    (

      SELECT generate_series(
        date_trunc('second', min(ticktime)) ,
        date_trunc('second', max(ticktime)) ,
        interval '1 sec'
      ) FROM  czces AS g(ticktime)

      LEFT JOIN
      (
        SELECT 
          date_trunc('second', ticktime) AS ticktime ,
          max(last_price) OVER w AS high ,
          min(last_price) OVER w AS low 
       FROM czces
       WHERE product_type ='TA' AND contract_month = '2014-08-01 00:00:00'::TIMESTAMP 
         WINDOW w AS (
          PARTITION BY date_trunc('second', ticktime)
          ORDER BY ticktime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          )
      ) t USING (ticktime)



      ORDER BY 1 
    ) ;
2
sqlpostgresql
I am not sure, but as I see, the error messege is self explanatory. So try to check: postgresql.org/docs/9.1/static/sql-set.html - vaso123
At ... FROM czces AS g (time) you are aliasing the table czces as g, not the sub-select. That's why g.time is bigint (because it's an alias of czces.id), and that's why you cannot use it in JOIN's USING condition. You could try with 1 more sub-select for the aggregated values. - pozs

2 Answers

1
votes

Assuming this subquery wants to produce a time column with rows for every seconds in an interval:

  SELECT generate_series(
    date_trunc('second', min(ticktime)) ,
    date_trunc('second', max(ticktime)) ,
    interval '1 sec'
  ) FROM czces  AS g (time)

it's wrong because g(time) with this syntax is supposed to refer to czces, rather than the output column.

You want this instead:

  SELECT generate_series(
    date_trunc('second', min(ticktime)) ,
    date_trunc('second', max(ticktime)) ,
    interval '1 sec'
  ) AS "time" FROM czces;
0
votes

If you missing alias x in ) AS ticktime FROM czces ) x

You will get missing alias erorr

    ERROR:  subquery in FROM must have an alias
    LINE 6: (
            ^
    HINT:  For example, FROM (SELECT ...) [AS] foo.

Workable version (But still not knowing how to padding NULL value with a default value)

    SELECT 
           x.ticktime,
           t.high,
           t.low
    FROM 
    (

      SELECT generate_series(
        date_trunc('second', min(ticktime)) ,
        date_trunc('second', max(ticktime)) ,
        interval '1 sec'
      ) AS ticktime FROM czces  ) x

      LEFT JOIN
      (
        SELECT 
          date_trunc('second', ticktime) AS ticktime ,
          max(last_price) OVER w AS high ,
          min(last_price) OVER w AS low 
       FROM czces
       WHERE product_type ='TA' AND contract_month = '2014-08-01 00:00:00'::TIMESTAMP 
         WINDOW w AS (
          PARTITION BY date_trunc('second', ticktime)
          ORDER BY ticktime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          )
      ) t USING (ticktime)


      ORDER BY 1 
    ;