Re: Way to "pivot"

[John Omernik]

Looks as though there is a JIRA, I added some of the notes here to the
comments:

https://issues.apache.org/jira/browse/DRILL-4223



On Tue, Mar 6, 2018 at 1:59 PM, Saurabh Mahapatra <
saurabhmahapatr...@gmail.com> wrote:

> Looks like SQL Server supports it, not sure if this is in the SQL standard:
>
> https://stackoverflow.com/questions/15931607/convert-
> rows-to-columns-using-pivot-in-sql-server
>
>
>
> On Tue, Mar 6, 2018 at 11:47 AM, Kunal Khatua <kunalkha...@gmail.com>
> wrote:
>
> > Not until now  :)
> >
> > Can you file a JIRA so that we can track it?
> >
> > On Tue, Mar 6, 2018 at 11:40 AM, John Omernik <j...@omernik.com> wrote:
> >
> > > Perfect.  That works for me because I have a limited number of values,
> I
> > > could see that getting out of hand if the values were unknown. Has
> there
> > > been any talk of a Pivot function in Drill? That would be helpful so
> you
> > > didn't have to know the column names ahead of time.
> > >
> > > John
> > >
> > > On Tue, Mar 6, 2018 at 10:50 AM, Ted Dunning <ted.dunn...@gmail.com>
> > > wrote:
> > >
> > > > Arjun's approach works even if the timestamps are not unique.
> > Especially
> > > if
> > > > you use avg instead of max.
> > > >
> > > > On Mar 6, 2018 8:47 AM, "Arjun kr" <arjun...@outlook.com> wrote:
> > > >
> > > > > If each timestamp has only one set of values for (x,y,z) , you can
> > try
> > > > > something like below.
> > > > >
> > > > > select dt ,
> > > > >  max(case when source='X' THEN `value` else 0.0 end) as X,
> > > > >  max(case when source='Y' THEN `value` else 0.0 end) as Y,
> > > > >  max(case when source='Z' THEN `value` else 0.0 end) as Z
> > > > > from
> > > > >  
> > > > > group by dt;
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Arjun
> > > > >
> > > > >
> > > > >
> > > > > 
> > > > > From: Andries Engelbrecht <aengelbre...@mapr.com>
> > > > > Sent: Tuesday, March 6, 2018 9:11 PM
> > > > > To: user@drill.apache.org
> > > > > Subject: Re: Way to "pivot"
> > > > >
> > > > > If the X, Y and Z is unique for each timestamp you can perhaps use
> > > group
> > > > > by (dt, X, Y , Z)  and case to make the X, Y , Z columns. May be
> > worth
> > > > > looking into, but is going to be expensive to execute. Just an
> idea,
> > > but
> > > > > have not tested it.
> > > > >
> > > > > --Andries
> > > > >
> > > > >
> > > > > On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:
> > > > >
> > > > > I am not sure if this is the right thing for what I am trying
> to
> > > do,
> > > > > but I
> > > > > have data in this formate
> > > > >
> > > > >
> > > > > source   dtvalue
> > > > > X2018-03-06 11:00 0.31
> > > > > X2018-03-06 12:00 0.94
> > > > > X2018-03-06 13:00 0.89
> > > > > X2018-03-06 14:00 0.01
> > > > > X2018-03-06 15:00 0.43
> > > > > Y2018-03-06 11:00 1.43
> > > > > Y2018-03-06 12:00 0.50
> > > > > Y2018-03-06 13:00 0.10
> > > > > Y2018-03-06 14:00 0.42
> > > > > Y2018-03-06 15:00 0.41
> > > > > Z2018-03-06 11:00 5.34
> > > > > Z2018-03-06 12:00 4.32
> > > > > Z2018-03-06 13:00 4.20
> > > > > Z2018-03-06 14:00 0.89
> > > > > Z2018-03-06 15:00 0.01
> > > > >
> > > > > I'd like to graph it as three lines (X, Y and Z) over time, so
> > the
> > > > > graph
> > > > > tool I am using asks for it this format:
> > > > >
> > > > >
> > > > >
> > > > > dt   X Y
> > > > >Z
> > > > >
> > > > > 2018-03-06 11:00 0.31   1.43 5.34
> > > > > 2018-03-06 12:00 0.94   0.50 4.32
> > > > > 2018-03-06 13:00 0.89   0.10 4.20
> > > > > 2018-03-06 14:00 0.01   0.42 0.89
> > > > > 2018-03-06 15:00 0.43   0.41 0.01
> > > > >
> > > > >
> > > > > So I think that would be a PIVOT like function right (which I
> > don't
> > > > > think
> > > > > Drill has) Is there a way to "fake" this in Drill using some
> > other
> > > > > built in
> > > > > functions?
> > > > >
> > > > > Thanks!
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > >
> > > >
> > >
> >
>

Re: Way to "pivot"

[Saurabh Mahapatra]

Looks like SQL Server supports it, not sure if this is in the SQL standard:

https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server



On Tue, Mar 6, 2018 at 11:47 AM, Kunal Khatua <kunalkha...@gmail.com> wrote:

> Not until now  :)
>
> Can you file a JIRA so that we can track it?
>
> On Tue, Mar 6, 2018 at 11:40 AM, John Omernik <j...@omernik.com> wrote:
>
> > Perfect.  That works for me because I have a limited number of values, I
> > could see that getting out of hand if the values were unknown. Has there
> > been any talk of a Pivot function in Drill? That would be helpful so you
> > didn't have to know the column names ahead of time.
> >
> > John
> >
> > On Tue, Mar 6, 2018 at 10:50 AM, Ted Dunning <ted.dunn...@gmail.com>
> > wrote:
> >
> > > Arjun's approach works even if the timestamps are not unique.
> Especially
> > if
> > > you use avg instead of max.
> > >
> > > On Mar 6, 2018 8:47 AM, "Arjun kr" <arjun...@outlook.com> wrote:
> > >
> > > > If each timestamp has only one set of values for (x,y,z) , you can
> try
> > > > something like below.
> > > >
> > > > select dt ,
> > > >  max(case when source='X' THEN `value` else 0.0 end) as X,
> > > >  max(case when source='Y' THEN `value` else 0.0 end) as Y,
> > > >  max(case when source='Z' THEN `value` else 0.0 end) as Z
> > > > from
> > > >  
> > > > group by dt;
> > > >
> > > > Thanks,
> > > >
> > > > Arjun
> > > >
> > > >
> > > >
> > > > 
> > > > From: Andries Engelbrecht <aengelbre...@mapr.com>
> > > > Sent: Tuesday, March 6, 2018 9:11 PM
> > > > To: user@drill.apache.org
> > > > Subject: Re: Way to "pivot"
> > > >
> > > > If the X, Y and Z is unique for each timestamp you can perhaps use
> > group
> > > > by (dt, X, Y , Z)  and case to make the X, Y , Z columns. May be
> worth
> > > > looking into, but is going to be expensive to execute. Just an idea,
> > but
> > > > have not tested it.
> > > >
> > > > --Andries
> > > >
> > > >
> > > > On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:
> > > >
> > > > I am not sure if this is the right thing for what I am trying to
> > do,
> > > > but I
> > > > have data in this formate
> > > >
> > > >
> > > > source   dtvalue
> > > > X2018-03-06 11:00 0.31
> > > > X2018-03-06 12:00 0.94
> > > > X2018-03-06 13:00 0.89
> > > > X2018-03-06 14:00 0.01
> > > > X2018-03-06 15:00 0.43
> > > > Y2018-03-06 11:00 1.43
> > > > Y2018-03-06 12:00 0.50
> > > > Y2018-03-06 13:00 0.10
> > > > Y2018-03-06 14:00 0.42
> > > > Y2018-03-06 15:00 0.41
> > > > Z2018-03-06 11:00 5.34
> > > > Z2018-03-06 12:00 4.32
> > > > Z2018-03-06 13:00 4.20
> > > > Z2018-03-06 14:00 0.89
> > > > Z2018-03-06 15:00 0.01
> > > >
> > > > I'd like to graph it as three lines (X, Y and Z) over time, so
> the
> > > > graph
> > > > tool I am using asks for it this format:
> > > >
> > > >
> > > >
> > > > dt   X Y
> > > >Z
> > > >
> > > > 2018-03-06 11:00 0.31   1.43 5.34
> > > > 2018-03-06 12:00 0.94   0.50 4.32
> > > > 2018-03-06 13:00 0.89   0.10 4.20
> > > > 2018-03-06 14:00 0.01   0.42 0.89
> > > > 2018-03-06 15:00 0.43   0.41 0.01
> > > >
> > > >
> > > > So I think that would be a PIVOT like function right (which I
> don't
> > > > think
> > > > Drill has) Is there a way to "fake" this in Drill using some
> other
> > > > built in
> > > > functions?
> > > >
> > > > Thanks!
> > > >
> > > > John
> > > >
> > > >
> > > >
> > >
> >
>

Re: Way to "pivot"

[Kunal Khatua]

Not until now  :)

Can you file a JIRA so that we can track it?

On Tue, Mar 6, 2018 at 11:40 AM, John Omernik <j...@omernik.com> wrote:

> Perfect.  That works for me because I have a limited number of values, I
> could see that getting out of hand if the values were unknown. Has there
> been any talk of a Pivot function in Drill? That would be helpful so you
> didn't have to know the column names ahead of time.
>
> John
>
> On Tue, Mar 6, 2018 at 10:50 AM, Ted Dunning <ted.dunn...@gmail.com>
> wrote:
>
> > Arjun's approach works even if the timestamps are not unique. Especially
> if
> > you use avg instead of max.
> >
> > On Mar 6, 2018 8:47 AM, "Arjun kr" <arjun...@outlook.com> wrote:
> >
> > > If each timestamp has only one set of values for (x,y,z) , you can try
> > > something like below.
> > >
> > > select dt ,
> > >  max(case when source='X' THEN `value` else 0.0 end) as X,
> > >  max(case when source='Y' THEN `value` else 0.0 end) as Y,
> > >  max(case when source='Z' THEN `value` else 0.0 end) as Z
> > > from
> > >  
> > > group by dt;
> > >
> > > Thanks,
> > >
> > > Arjun
> > >
> > >
> > >
> > > 
> > > From: Andries Engelbrecht <aengelbre...@mapr.com>
> > > Sent: Tuesday, March 6, 2018 9:11 PM
> > > To: user@drill.apache.org
> > > Subject: Re: Way to "pivot"
> > >
> > > If the X, Y and Z is unique for each timestamp you can perhaps use
> group
> > > by (dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth
> > > looking into, but is going to be expensive to execute. Just an idea,
> but
> > > have not tested it.
> > >
> > > --Andries
> > >
> > >
> > > On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:
> > >
> > > I am not sure if this is the right thing for what I am trying to
> do,
> > > but I
> > > have data in this formate
> > >
> > >
> > > source   dtvalue
> > > X2018-03-06 11:00 0.31
> > > X2018-03-06 12:00 0.94
> > > X2018-03-06 13:00 0.89
> > > X2018-03-06 14:00 0.01
> > > X2018-03-06 15:00 0.43
> > > Y2018-03-06 11:00 1.43
> > > Y2018-03-06 12:00 0.50
> > > Y2018-03-06 13:00 0.10
> > > Y2018-03-06 14:00 0.42
> > > Y2018-03-06 15:00 0.41
> > > Z2018-03-06 11:00 5.34
> > > Z2018-03-06 12:00 4.32
> > > Z2018-03-06 13:00 4.20
> > > Z2018-03-06 14:00 0.89
> > > Z2018-03-06 15:00 0.01
> > >
> > > I'd like to graph it as three lines (X, Y and Z) over time, so the
> > > graph
> > > tool I am using asks for it this format:
> > >
> > >
> > >
> > > dt   X Y
> > >Z
> > >
> > > 2018-03-06 11:00 0.31   1.43 5.34
> > > 2018-03-06 12:00 0.94   0.50 4.32
> > > 2018-03-06 13:00 0.89   0.10 4.20
> > > 2018-03-06 14:00 0.01   0.42 0.89
> > > 2018-03-06 15:00 0.43   0.41 0.01
> > >
> > >
> > > So I think that would be a PIVOT like function right (which I don't
> > > think
> > > Drill has) Is there a way to "fake" this in Drill using some other
> > > built in
> > > functions?
> > >
> > > Thanks!
> > >
> > > John
> > >
> > >
> > >
> >
>

Re: Way to "pivot"

[John Omernik]

Perfect.  That works for me because I have a limited number of values, I
could see that getting out of hand if the values were unknown. Has there
been any talk of a Pivot function in Drill? That would be helpful so you
didn't have to know the column names ahead of time.

John

On Tue, Mar 6, 2018 at 10:50 AM, Ted Dunning <ted.dunn...@gmail.com> wrote:

> Arjun's approach works even if the timestamps are not unique. Especially if
> you use avg instead of max.
>
> On Mar 6, 2018 8:47 AM, "Arjun kr" <arjun...@outlook.com> wrote:
>
> > If each timestamp has only one set of values for (x,y,z) , you can try
> > something like below.
> >
> > select dt ,
> >  max(case when source='X' THEN `value` else 0.0 end) as X,
> >  max(case when source='Y' THEN `value` else 0.0 end) as Y,
> >  max(case when source='Z' THEN `value` else 0.0 end) as Z
> > from
> >  
> > group by dt;
> >
> > Thanks,
> >
> > Arjun
> >
> >
> >
> > ____
> > From: Andries Engelbrecht <aengelbre...@mapr.com>
> > Sent: Tuesday, March 6, 2018 9:11 PM
> > To: user@drill.apache.org
> > Subject: Re: Way to "pivot"
> >
> > If the X, Y and Z is unique for each timestamp you can perhaps use group
> > by (dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth
> > looking into, but is going to be expensive to execute. Just an idea, but
> > have not tested it.
> >
> > --Andries
> >
> >
> > On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:
> >
> > I am not sure if this is the right thing for what I am trying to do,
> > but I
> > have data in this formate
> >
> >
> > source   dtvalue
> > X2018-03-06 11:00 0.31
> > X2018-03-06 12:00 0.94
> > X2018-03-06 13:00 0.89
> > X2018-03-06 14:00 0.01
> > X2018-03-06 15:00 0.43
> > Y2018-03-06 11:00 1.43
> > Y2018-03-06 12:00 0.50
> > Y2018-03-06 13:00 0.10
> > Y2018-03-06 14:00 0.42
> > Y2018-03-06 15:00 0.41
> > Z2018-03-06 11:00 5.34
> > Z2018-03-06 12:00 4.32
> > Z2018-03-06 13:00 4.20
> > Z2018-03-06 14:00 0.89
> > Z2018-03-06 15:00 0.01
> >
> > I'd like to graph it as three lines (X, Y and Z) over time, so the
> > graph
> > tool I am using asks for it this format:
> >
> >
> >
> > dt   X Y
> >Z
> >
> > 2018-03-06 11:00 0.31   1.43 5.34
> > 2018-03-06 12:00 0.94   0.50 4.32
> > 2018-03-06 13:00 0.89   0.10 4.20
> > 2018-03-06 14:00 0.01   0.42 0.89
> > 2018-03-06 15:00 0.43   0.41 0.01
> >
> >
> > So I think that would be a PIVOT like function right (which I don't
> > think
> > Drill has) Is there a way to "fake" this in Drill using some other
> > built in
> > functions?
> >
> > Thanks!
> >
> > John
> >
> >
> >
>

Re: Way to "pivot"

[Ted Dunning]

Arjun's approach works even if the timestamps are not unique. Especially if
you use avg instead of max.

On Mar 6, 2018 8:47 AM, "Arjun kr" <arjun...@outlook.com> wrote:

> If each timestamp has only one set of values for (x,y,z) , you can try
> something like below.
>
> select dt ,
>  max(case when source='X' THEN `value` else 0.0 end) as X,
>  max(case when source='Y' THEN `value` else 0.0 end) as Y,
>  max(case when source='Z' THEN `value` else 0.0 end) as Z
> from
>  
> group by dt;
>
> Thanks,
>
> Arjun
>
>
>
> 
> From: Andries Engelbrecht <aengelbre...@mapr.com>
> Sent: Tuesday, March 6, 2018 9:11 PM
> To: user@drill.apache.org
> Subject: Re: Way to "pivot"
>
> If the X, Y and Z is unique for each timestamp you can perhaps use group
> by (dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth
> looking into, but is going to be expensive to execute. Just an idea, but
> have not tested it.
>
> --Andries
>
>
> On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:
>
> I am not sure if this is the right thing for what I am trying to do,
> but I
> have data in this formate
>
>
> source   dtvalue
> X2018-03-06 11:00 0.31
> X2018-03-06 12:00 0.94
> X2018-03-06 13:00 0.89
> X2018-03-06 14:00 0.01
> X2018-03-06 15:00 0.43
> Y2018-03-06 11:00 1.43
> Y2018-03-06 12:00 0.50
> Y2018-03-06 13:00 0.10
> Y2018-03-06 14:00 0.42
> Y2018-03-06 15:00 0.41
> Z2018-03-06 11:00 5.34
> Z2018-03-06 12:00 4.32
> Z2018-03-06 13:00 4.20
> Z2018-03-06 14:00 0.89
> Z2018-03-06 15:00 0.01
>
> I'd like to graph it as three lines (X, Y and Z) over time, so the
> graph
> tool I am using asks for it this format:
>
>
>
> dt   X Y
>Z
>
> 2018-03-06 11:00 0.31   1.43 5.34
> 2018-03-06 12:00 0.94   0.50 4.32
> 2018-03-06 13:00 0.89   0.10 4.20
> 2018-03-06 14:00 0.01   0.42 0.89
> 2018-03-06 15:00 0.43   0.41 0.01
>
>
> So I think that would be a PIVOT like function right (which I don't
> think
> Drill has) Is there a way to "fake" this in Drill using some other
> built in
> functions?
>
> Thanks!
>
> John
>
>
>

Re: Way to "pivot"

[Arjun kr]

If each timestamp has only one set of values for (x,y,z) , you can try 
something like below.

select dt ,
 max(case when source='X' THEN `value` else 0.0 end) as X,
 max(case when source='Y' THEN `value` else 0.0 end) as Y,
 max(case when source='Z' THEN `value` else 0.0 end) as Z
from
 
group by dt;

Thanks,

Arjun




From: Andries Engelbrecht <aengelbre...@mapr.com>
Sent: Tuesday, March 6, 2018 9:11 PM
To: user@drill.apache.org
Subject: Re: Way to "pivot"

If the X, Y and Z is unique for each timestamp you can perhaps use group by 
(dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth looking 
into, but is going to be expensive to execute. Just an idea, but have not 
tested it.

--Andries


On 3/6/18, 6:46 AM, "John Omernik" <j...@omernik.com> wrote:

I am not sure if this is the right thing for what I am trying to do, but I
have data in this formate


source   dtvalue
X2018-03-06 11:00 0.31
X2018-03-06 12:00 0.94
X2018-03-06 13:00 0.89
X2018-03-06 14:00 0.01
X2018-03-06 15:00 0.43
Y2018-03-06 11:00 1.43
Y2018-03-06 12:00 0.50
Y2018-03-06 13:00 0.10
Y2018-03-06 14:00 0.42
Y2018-03-06 15:00 0.41
Z2018-03-06 11:00 5.34
Z2018-03-06 12:00 4.32
Z2018-03-06 13:00 4.20
Z2018-03-06 14:00 0.89
Z2018-03-06 15:00 0.01

I'd like to graph it as three lines (X, Y and Z) over time, so the graph
tool I am using asks for it this format:



dt   X Y Z

2018-03-06 11:00 0.31   1.43 5.34
2018-03-06 12:00 0.94   0.50 4.32
2018-03-06 13:00 0.89   0.10 4.20
2018-03-06 14:00 0.01   0.42 0.89
2018-03-06 15:00 0.43   0.41 0.01


So I think that would be a PIVOT like function right (which I don't think
Drill has) Is there a way to "fake" this in Drill using some other built in
functions?

Thanks!

John

Re: Way to "pivot"

[Andries Engelbrecht]

If the X, Y and Z is unique for each timestamp you can perhaps use group by 
(dt, X, Y , Z)  and case to make the X, Y , Z columns. May be worth looking 
into, but is going to be expensive to execute. Just an idea, but have not 
tested it.

--Andries


On 3/6/18, 6:46 AM, "John Omernik"  wrote:

I am not sure if this is the right thing for what I am trying to do, but I
have data in this formate


source   dtvalue
X2018-03-06 11:00 0.31
X2018-03-06 12:00 0.94
X2018-03-06 13:00 0.89
X2018-03-06 14:00 0.01
X2018-03-06 15:00 0.43
Y2018-03-06 11:00 1.43
Y2018-03-06 12:00 0.50
Y2018-03-06 13:00 0.10
Y2018-03-06 14:00 0.42
Y2018-03-06 15:00 0.41
Z2018-03-06 11:00 5.34
Z2018-03-06 12:00 4.32
Z2018-03-06 13:00 4.20
Z2018-03-06 14:00 0.89
Z2018-03-06 15:00 0.01

I'd like to graph it as three lines (X, Y and Z) over time, so the graph
tool I am using asks for it this format:



dt   X Y Z

2018-03-06 11:00 0.31   1.43 5.34
2018-03-06 12:00 0.94   0.50 4.32
2018-03-06 13:00 0.89   0.10 4.20
2018-03-06 14:00 0.01   0.42 0.89
2018-03-06 15:00 0.43   0.41 0.01


So I think that would be a PIVOT like function right (which I don't think
Drill has) Is there a way to "fake" this in Drill using some other built in
functions?

Thanks!

John

Way to "pivot"

[John Omernik]

I am not sure if this is the right thing for what I am trying to do, but I
have data in this formate


source   dtvalue
X2018-03-06 11:00 0.31
X2018-03-06 12:00 0.94
X2018-03-06 13:00 0.89
X2018-03-06 14:00 0.01
X2018-03-06 15:00 0.43
Y2018-03-06 11:00 1.43
Y2018-03-06 12:00 0.50
Y2018-03-06 13:00 0.10
Y2018-03-06 14:00 0.42
Y2018-03-06 15:00 0.41
Z2018-03-06 11:00 5.34
Z2018-03-06 12:00 4.32
Z2018-03-06 13:00 4.20
Z2018-03-06 14:00 0.89
Z2018-03-06 15:00 0.01

I'd like to graph it as three lines (X, Y and Z) over time, so the graph
tool I am using asks for it this format:



dt   X Y Z

2018-03-06 11:00 0.31   1.43 5.34
2018-03-06 12:00 0.94   0.50 4.32
2018-03-06 13:00 0.89   0.10 4.20
2018-03-06 14:00 0.01   0.42 0.89
2018-03-06 15:00 0.43   0.41 0.01


So I think that would be a PIVOT like function right (which I don't think
Drill has) Is there a way to "fake" this in Drill using some other built in
functions?

Thanks!

John