0

Let's consider I have table like this :

     id   name  addr_line 1    addr_line_2  rec_ins_dt   rec_updt_dt

and I want to show output as follows :

     rec_ins_dt  rec_ins_dt_count  rec_updt_dt  rec_updt_dt_count

How can I achieve this result using single query ? I understand this can be done by creating temp tables and then joining two temp tables together but I want to use single query. Following are the additional limitations while executing this query :

Input data : 1 billion rows Memory : 4 GB

Please consider platform as Oracle or Netezza. Thank you for your inputs.

Vishal
  • 279
  • 3
  • 8
  • 18

1 Answers1

1
SELECT 
    rec_ins_dt , COUNT(*) OVER (PARTITION BY rec_ins_dt) AS rec_ins_dt_count,
    rec_updt_dt , COUNT(*) OVER (PARTITION BY rec_updt_dt) AS rec_ins_dt_count
FROM <your-table>;

Oracle Version

MathCurious
  • 174
  • 4
  • Isn't this going to return a billion rows because you have no `GROUP BY`? – WW. Jul 09 '14 at 02:11
  • you might want to look [here](http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions039.htm#SQLRF51318) @WW – MathCurious Jul 09 '14 at 02:20
  • Still going to return a billion rows – WW. Jul 09 '14 at 07:05
  • 1
    It's true that this will return a billion rows, however the data will still be accurate (although including redundant data) as window functions manage their own independent "group by" functionality with the "partition by" clause. Changing MathCurious's answer to be a SELECT DISTINCT would eliminate that redundancy if doing so would be desirable. – ScottMcG Jul 11 '14 at 15:54