How can i optimize the below query?

Question

I have a table like this.

_id (integer)
event_name(varchar(20))
event_date(timestamp)

Here is some sample data given below.

ID         event_date                          event_name
101        2013-04-24 18:33:37.694818          event_A
102        2013-04-24 20:34:37.000000          event_B
103        2013-04-24 20:40:37.000000          event_A
104        2013-04-25 01:00:00.694818          event_B
105        2013-04-25 12:00:15.694818          event_A

I need the data from above table in below format.

Date          count_eventA                       count_eventB
2013-04-24    2                                   1
2013-04-25    1                                   1

hence basically in need the count of each event on each date.

I have tried below query for getting the desired result.

SELECT A.date1 AS Date ,
       A.count1 AS count_eventA,
       B.count2 AS count_eventB,
           FROM
           (SELECT count(event_name)AS count1,
            event_date::date AS date1
            FROM tblname
            WHERE event_name='event_A'
            GROUP BY (event_date::date))AS A 
           LEFT JOIN
           (SELECT count(event_name)AS count1,
            event_date::date AS date1
            FROM tblname
            WHERE event_name='event_B'
            GROUP BY (event_date::date))AS B ON A.date1=B.date2

Can someone please suggest me to find out a better and optimized query? , or I am following a good approach .

There can be more than 2 events , but right now I need to care for only 2 event. — Anant, Jun 06 '13 at 09:43
PostgreSQL version? `EXPLAIN ANALYZE`? See http://stackoverflow.com/tags/postgresql-performance/info — Craig Ringer, Jun 06 '13 at 09:50
@Anant 9.0.0 ? Well, that won't help. http://www.postgresql.org/support/versioning/ . The current patch release is 9.0.13; you're missing at least one index corruption bug fix and a **major** security bug fix per http://www.postgresql.org/support/security/faq/2013-04-04/ . You're missing two and a half years worth of patches... — Craig Ringer, Jun 06 '13 at 10:04
@CraigRinger . thanks . but i am working on local machine, and just want to get more efficient query? on live server i guess the version is updated.. — Anant, Jun 06 '13 at 10:10
@Anant Best to use the same version on local and live, otherwise you're not testing in the same environment you're deploying to. Badness results. — Craig Ringer, Jun 06 '13 at 10:24
@CraigRinger ..thanks. I will synchronize the local and live postgres version. — Anant, Jun 06 '13 at 10:26

Zelloss · Accepted Answer · 2013-06-06T11:14:43.553

3

Something on this lines should work:

select event_date::date AS Date ,
        count_eventA = sum(case when event_name = 'event_A' then 1 else 0 end),
     count_eventB = sum(case when event_name = 'event_B' then 1 else 0 end)
from tblname
GROUP BY (event_date::date))

If you have more events you only need to add more sum(case) lines :)

The DBEngine only runs through the table once to give you the totals, independiently of the number of the events you want to count: when you have a high rowcount you will observe significant delay with the original query. Should I add this to my answer, you think

edited Jun 06 '13 at 11:14

answered Jun 06 '13 at 09:53

Zelloss

568
3
12

can you please explain little more , how this would be the better approach? – Anant Jun 06 '13 at 10:06
The DBEngine only runs through the table once to give you the totals, independiently of the number of the events you want to count: when you have a high rowcount you will observe significant delay with the original query. Should I add this to my answer, you think? – Zelloss Jun 06 '13 at 10:09
1

thanks @Zelloss .. for the nice answer !! and yes , it would be better if you add the above description in the answers also. – Anant Jun 06 '13 at 10:14

score 1 · Answer 2 · answered Jun 06 '13 at 11:24

1

Simpler (and cleaner) than the case syntax:

select
    event_date::date as Date,
    count(event_name = 'event_A' or null) count_eventA,
    count(event_name = 'event_B' or null) count_eventB
from t
group by 1

answered Jun 06 '13 at 11:24

Clodoaldo Neto

118,695
26
233
260

thanks for the answer. Can you please explain in your answer, when it is the matter of performance, how above query works better than "CASE Syntax query" ? – Anant Jun 06 '13 at 11:41
1

@Anant I guess the performance will be very similar if not exactly the same. – Clodoaldo Neto Jun 06 '13 at 11:44

score 0 · Answer 3 · answered Jun 06 '13 at 10:22

0

you are looking for PIVOT and UNPIVOT in sql check below example is very handy

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

answered Jun 06 '13 at 10:22

JSJ

5,653
3
25
32

2

Not on PostgreSQL they're not, as those commands do not exist. – Craig Ringer Jun 06 '13 at 10:25

How can i optimize the below query?

3 Answers3