z

    Zahlii

    1 month ago
    And maybe yet another question - Can anyone think of a simple solution how to solve this with QDB: Given an aggregation over time , find the max value for a column as well as the timestamp assigned to this max value? E.g. in my use case weather data, I want to find the date in which the maximum temperature was reached for each year
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    Do select with order by desc limit -1
    z

    Zahlii

    1 month ago
    Something like select first(date), first(value) from bla Sample by date order by value desc?
    Alex Pelagenko

    Alex Pelagenko

    1 month ago
    Hmm, no, will not work with sample by
    Select temperature, timestamp 
    from weather
     where timestamp in '2021' 
    order by temperature desc 
    limit -1
    z

    Zahlii

    1 month ago
    Yeah, but then I would need to do that "by hand" for every year...or issue multiple queries. Was hoping there may be a smart way on doing all that in qdb
    Bolek Ziobrowski

    Bolek Ziobrowski

    1 month ago
    @Zahlii Try this :
    create table weather2 
    (
     timestamp timestamp,
     temperature float
    ) timestamp(timestamp) partition by year;
    
    
    insert into weather2
    select cast(x*360000000 as timestamp), 
           rnd_float(0)*100 from long_sequence(1000000);
    
    select  timestamp_floor('y', timestamp), max(temperature) 
    from weather2 
    group by timestamp_floor('y', timestamp);  
    
    1970-01-01T00:00:00.000000Z 99.9987
    1971-01-01T00:00:00.000000Z 99.9989
    1972-01-01T00:00:00.000000Z 99.9996
    1973-01-01T00:00:00.000000Z 99.9997
    1974-01-01T00:00:00.000000Z 99.9988
    1975-01-01T00:00:00.000000Z 99.9985
    1976-01-01T00:00:00.000000Z 99.9992
    1977-01-01T00:00:00.000000Z 99.9986
    1978-01-01T00:00:00.000000Z 99.9999
    1979-01-01T00:00:00.000000Z 99.9999
    1980-01-01T00:00:00.000000Z 99.9992
    1981-01-01T00:00:00.000000Z 99.9994
    
    selecT timestamp, temperature from 
    ( 
      Select temperature, timestamp, 
             row_number() over (partition by timestamp_floor('y', timestamp) order by temperature desc, timestamp )  rid 
      from weather2 
    ) inq 
    where rid = 0 
    order by timestamp
    
    1970-11-01T11:30:00.000000Z 99.9987
    1971-09-23T19:48:00.000000Z 99.9989
    1972-04-06T15:48:00.000000Z 99.9996
    1973-01-28T04:18:00.000000Z 99.9997
    1974-08-05T12:30:00.000000Z 99.9988
    1975-10-03T11:54:00.000000Z 99.9985
    1976-08-31T05:30:00.000000Z 99.9992
    1977-02-06T22:12:00.000000Z 99.9986
    1978-07-02T08:06:00.000000Z 99.9999
    1979-10-01T19:24:00.000000Z 99.9999
    1980-02-08T00:00:00.000000Z 99.9992
    1981-01-29T07:06:00.000000Z 99.9994
    
    selecT * from weather2 where timestamp = '1970-11-01T11:30:00.000000Z'
    
    1970-11-01T11:30:00.000000Z 99.9987