Comments

6 comments

  • Avatar
    Candido Dessanti

    HI @kimgiftww ,

    Welcome to the community!

    We haven't PIVOT or UNPIVOT function on our database, but you can "emulate" them if you know the values in advance

    e.g.

    select uniquecarrier, 
           round(avg(case when flight_dayofweek = 1 then depdelay else null end),1) as mon, 
           round(avg(case when flight_dayofweek = 2 then depdelay else null end),1) as tue,
           round(avg(case when flight_dayofweek = 3 then depdelay else null end),1) as wed,
           round(avg(case when flight_dayofweek = 4 then depdelay else null end),1) as thu,
           round(avg(case when flight_dayofweek = 5 then depdelay else null end),1) as fri,
           round(avg(case when flight_dayofweek = 6 then depdelay else null end),1) as sat,
           round(avg(case when flight_dayofweek = 7 then depdelay else null end),1) as sun
    From flights_2008_7m group by 1;
    

    Regards, Candido

    0
    Comment actions Permalink
  • Avatar
    Skunpoj Thanarojsophon

    Hi, thanks for sharing.

    I am using pivot because I don't know the value to be pivoted as columns in advance. The values are hundreds of distinct dates. This is why I am looking for the pivot functionality.

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Hi,

    I'm going to ask internally, but I think that it's not pèossible to add such functionality with a custom table function; probably it's doable programmatically with python (?).

    I will come back to you if I had something to share.

    Candido

    0
    Comment actions Permalink
  • Avatar
    Skunpoj Thanarojsophon

    Hi,

    Thanks for taking it to consideration. I am currently do it with pandas and was looking for a way to do it using GPU. (Will also try Dask or other parallelisation tools)

    As I was using heavydb as our data warehouse, I thought this was something we could do it during the data transformation process.

    Doing the pivot in python would require exporting the whole data to fit into cuDF and this may be something we could not afford.

    We would like to utilise and rely on the layer (gpu ram disk) optimization that HeavyDB provides.

    0
    Comment actions Permalink
  • Avatar
    Skunpoj Thanarojsophon

    Instead of the pivot, is it possible to keep the result of the groupby aggregation to an array column?

    0
    Comment actions Permalink
  • Avatar
    Candido Dessanti

    Yap, probably this is doable with an UDTF.

    The reply is maybe ;)

    0
    Comment actions Permalink

Please sign in to leave a comment.