Обсуждение: Ordering a date_part() query ...
I have a query that looks like:
SELECT ( date_part('month', stat_period) || '/' || date_part('day', stat_period) || '/' ||
date_part('year',stat_period) || ' ' || date_part('hour', stat_period) || ':00:00' ) as "Summary - by
Day/Hour", sum(impressions) as impressions, sum(click_thru) as click_thru FROM banner_count
GROUP BY "Summary - by Day/Hour";
Works great, except that the output looks like:
Summary - by Day/Hour|impressions|click_thru
---------------------+-----------+----------
1/17/2000 23:00:00 | 63| 1
1/18/2000 0:00:00 | 151| 0
1/18/2000 10:00:00 | 348| 4
1/18/2000 11:00:00 | 804| 1
1/18/2000 12:00:00 | 180| 1
1/18/2000 1:00:00 | 89| 0
1/18/2000 2:00:00 | 112| 0
1/18/2000 3:00:00 | 178| 0
1/18/2000 4:00:00 | 184| 0
1/18/2000 5:00:00 | 133| 0
1/18/2000 6:00:00 | 119| 0
1/18/2000 7:00:00 | 148| 0
1/18/2000 8:00:00 | 224| 0
1/18/2000 9:00:00 | 272| 0
Notice the 10/11/12/etc hour stats come before the 1am stats?
So its sorting okay on the date, but I need to get it to d it on the time
as well..
I'm still playing with it, but if someone can suggest a means of doing
this, I'm all ears :)
Thanks...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker wrote:
>
> I have a query that looks like:
>
> SELECT ( date_part('month', stat_period) || '/' ||
> date_part('day', stat_period) || '/' ||
> date_part('year', stat_period) || ' ' ||
> date_part('hour', stat_period) || ':00:00' ) as "Summary - by
> Day/Hour",
> sum(impressions) as impressions, sum(click_thru) as click_thru
> FROM banner_count
> GROUP BY "Summary - by Day/Hour";
>
Try appending ORDER BY "Summary - by Day/Hour"::date
to your query.
Mark
> The Hermit Hacker wrote:
> >
> > I have a query that looks like:
> >
> > SELECT ( date_part('month', stat_period) || '/' ||
> > date_part('day', stat_period) || '/' ||
> > date_part('year', stat_period) || ' ' ||
> > date_part('hour', stat_period) || ':00:00' ) as "Summary - by
> > Day/Hour",
> > sum(impressions) as impressions, sum(click_thru) as click_thru
> > FROM banner_count
> > GROUP BY "Summary - by Day/Hour";
> >
>
> Try appending ORDER BY "Summary - by Day/Hour"::date
> to your query.
Oh, much better than my CASE idea. I thought he was sorting date/time
strings, not datetime values.
-- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
You may need to use CASE to output a column to be sorted on.
>
> I have a query that looks like:
>
> SELECT ( date_part('month', stat_period) || '/' ||
> date_part('day', stat_period) || '/' ||
> date_part('year', stat_period) || ' ' ||
> date_part('hour', stat_period) || ':00:00' ) as "Summary - by
> Day/Hour",
> sum(impressions) as impressions, sum(click_thru) as click_thru
> FROM banner_count
> GROUP BY "Summary - by Day/Hour";
>
>
> Works great, except that the output looks like:
>
> Summary - by Day/Hour|impressions|click_thru
> ---------------------+-----------+----------
> 1/17/2000 23:00:00 | 63| 1
> 1/18/2000 0:00:00 | 151| 0
> 1/18/2000 10:00:00 | 348| 4
> 1/18/2000 11:00:00 | 804| 1
> 1/18/2000 12:00:00 | 180| 1
> 1/18/2000 1:00:00 | 89| 0
> 1/18/2000 2:00:00 | 112| 0
> 1/18/2000 3:00:00 | 178| 0
> 1/18/2000 4:00:00 | 184| 0
> 1/18/2000 5:00:00 | 133| 0
> 1/18/2000 6:00:00 | 119| 0
> 1/18/2000 7:00:00 | 148| 0
> 1/18/2000 8:00:00 | 224| 0
> 1/18/2000 9:00:00 | 272| 0
>
> Notice the 10/11/12/etc hour stats come before the 1am stats?
>
> So its sorting okay on the date, but I need to get it to d it on the time
> as well..
>
> I'm still playing with it, but if someone can suggest a means of doing
> this, I'm all ears :)
>
> Thanks...
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
>
>
> ************
>
>
-- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
INSERT INTO my_brain VALUES ('clue') :-)
Whoops, Bruce's response reminded me,
what I meant to say was
ORDER BY "Summary - by Day/Hour"::datetime
Mark
The Hermit Hacker wrote:
>
> I have a query that looks like:
>
> SELECT ( date_part('month', stat_period) || '/' ||
> date_part('day', stat_period) || '/' ||
> date_part('year', stat_period) || ' ' ||
> date_part('hour', stat_period) || ':00:00' ) as "Summary - by
> Day/Hour",
> sum(impressions) as impressions, sum(click_thru) as click_thru
> FROM banner_count
> GROUP BY "Summary - by Day/Hour";
>
> INSERT INTO my_brain VALUES ('clue') :-)
>
> Whoops, Bruce's response reminded me,
> what I meant to say was
> ORDER BY "Summary - by Day/Hour"::datetime
Yea, I was right. He is constructing a string here.
>
> Mark
>
> The Hermit Hacker wrote:
> >
> > I have a query that looks like:
> >
> > SELECT ( date_part('month', stat_period) || '/' ||
> > date_part('day', stat_period) || '/' ||
> > date_part('year', stat_period) || ' ' ||
> > date_part('hour', stat_period) || ':00:00' ) as "Summary - by
> > Day/Hour",
> > sum(impressions) as impressions, sum(click_thru) as click_thru
> > FROM banner_count
> > GROUP BY "Summary - by Day/Hour";
> >
>
> ************
>
>
-- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Had thought about that one:
SELECT ( date_part('month', stat_period) || '/' || date_part('day', stat_period) || '/' ||
date_part('year',stat_period) || ' ' || date_part('hour', stat_period) || ':00:00' ) as "Summary - by
Day/Hour", sum(impressions) as impressions, sum(click_thru) as click_thru FROM banner_count
GROUP BY "Summary - by Day/Hour"
ORDER BY "Summary - by Day/Hour"::datetime;
results in:
ERROR: attribute 'Summary - by Day/Hour' not found
But, that gave me an idea...
SELECT ( date_part('month', stat_period) || '/' || date_part('day', stat_period) || '/' ||
date_part('year',stat_period) || ' ' || date_part('hour', stat_period) || ':00:00' )::datetime as day,
sum(impressions)as impressions, sum(click_thru) as click_thru FROM banner_count
GROUP BY day;
CASTing the results gives me what I want, and gives cleaner lookign
results then what I had :)
Summary - by Day/Hour |impressions|click_thru
----------------------------+-----------+----------
Mon Jan 17 23:00:00 2000 EST| 63| 1
Tue Jan 18 00:00:00 2000 EST| 151| 0
Tue Jan 18 01:00:00 2000 EST| 89| 0
Tue Jan 18 02:00:00 2000 EST| 112| 0
Tue Jan 18 03:00:00 2000 EST| 178| 0
Tue Jan 18 04:00:00 2000 EST| 184| 0
Tue Jan 18 05:00:00 2000 EST| 133| 0
Tue Jan 18 06:00:00 2000 EST| 119| 0
Tue Jan 18 07:00:00 2000 EST| 148| 0
Tue Jan 18 08:00:00 2000 EST| 224| 0
Tue Jan 18 09:00:00 2000 EST| 272| 0
Tue Jan 18 10:00:00 2000 EST| 348| 4
Tue Jan 18 11:00:00 2000 EST| 804| 1
Tue Jan 18 12:00:00 2000 EST| 632| 1
Tue Jan 18 13:00:00 2000 EST| 1200| 0
Tue Jan 18 14:00:00 2000 EST| 110| 0
On Tue, 18 Jan 2000, Mark Volpe wrote:
> INSERT INTO my_brain VALUES ('clue') :-)
>
> Whoops, Bruce's response reminded me,
> what I meant to say was
> ORDER BY "Summary - by Day/Hour"::datetime
>
> Mark
>
> The Hermit Hacker wrote:
> >
> > I have a query that looks like:
> >
> > SELECT ( date_part('month', stat_period) || '/' ||
> > date_part('day', stat_period) || '/' ||
> > date_part('year', stat_period) || ' ' ||
> > date_part('hour', stat_period) || ':00:00' ) as "Summary - by
> > Day/Hour",
> > sum(impressions) as impressions, sum(click_thru) as click_thru
> > FROM banner_count
> > GROUP BY "Summary - by Day/Hour";
> >
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
> SELECT ( date_part('month', stat_period) || '/' ||
> date_part('day', stat_period) || '/' ||
> date_part('year', stat_period) || ' ' ||
> date_part('hour', stat_period) || ':00:00' ) as "Summary - by Day/Hour",
> sum(impressions) as impressions, sum(click_thru) as click_thru
> FROM banner_count
> GROUP BY "Summary - by Day/Hour"
> ORDER BY "Summary - by Day/Hour"::datetime;
Uh, why don't you just GROUP BY and ORDER BY stat_period?
I also wonder whether you can't find a combination of date_trunc and
datestyle that will produce the output format you want. Doing it
like the above sure seems like using the wrong tool for the job...
regards, tom lane
On Tue, 18 Jan 2000, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> > SELECT ( date_part('month', stat_period) || '/' ||
> > date_part('day', stat_period) || '/' ||
> > date_part('year', stat_period) || ' ' ||
> > date_part('hour', stat_period) || ':00:00' ) as "Summary - by Day/Hour",
> > sum(impressions) as impressions, sum(click_thru) as click_thru
> > FROM banner_count
> > GROUP BY "Summary - by Day/Hour"
> > ORDER BY "Summary - by Day/Hour"::datetime;
>
>
> Uh, why don't you just GROUP BY and ORDER BY stat_period?
Okay, maybe I'm misunderstanding GROUP BY, but my understanding is that it
groups "like" values ... my above example would give me a count of all
impressions received over a one hour period ...
To use stat_period, I'd get one row for each 'stat_period' value, which,
since its in seconds, would mean every record in the table...
The above takes off the min/secs and then I group by hours ...
> I also wonder whether you can't find a combination of date_trunc and
> datestyle that will produce the output format you want. Doing it
> like the above sure seems like using the wrong tool for the job...
It is quite possible ... I'm not used to using functions much so far,
first time I've really had a use for it...
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker <scrappy@hub.org> writes:
>> Uh, why don't you just GROUP BY and ORDER BY stat_period?
> Okay, maybe I'm misunderstanding GROUP BY, but my understanding is that it
> groups "like" values ... my above example would give me a count of all
> impressions received over a one hour period ...
Oh, so "stat_period" is not a period label but an exact timestamp,
and you want to group into hourly blocks? OK, then group/order by
date_trunc("hour", stat_period) is probably what you want...
I'd suggest choosing another name for the field, too ;-)
regards, tom lane
On Tue, 18 Jan 2000, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> >> Uh, why don't you just GROUP BY and ORDER BY stat_period?
>
> > Okay, maybe I'm misunderstanding GROUP BY, but my understanding is that it
> > groups "like" values ... my above example would give me a count of all
> > impressions received over a one hour period ...
>
> Oh, so "stat_period" is not a period label but an exact timestamp,
> and you want to group into hourly blocks? OK, then group/order by
> date_trunc("hour", stat_period) is probably what you want...
good, ya had me worried I missed something in the docs on this one :)
> I'd suggest choosing another name for the field, too ;-)
*grin* I'm the only one that accesses it, so it doesn't much matter
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org