Re^3: Terminal decline? (Oracle-2-PostgreSQL port)

That took way too long to put together. :)

Nice!

Now it was easy for me to port to postgres (and it had to be 9.5devel as only in that (not-yet-released) version does rollup exist).

(From the combination of dual and rollup I think I can conclude you concocted the above SQL on Oracle)

(PostgreSQL 9.5 development is here: git://git.postgresql.org/git/postgresql.git ((it's pretty stable, always, and especially now that it is in feature-freeze (but NOT(!) for production of course)).

I formatted it a bit to my and postgres' taste and I wrapped the whole thing in some bash tomfoolery to run the three CTE main-selects consecutively:

./polls.sh:
[download]

#!/bin/sh

mainsql[1]=''
mainsql[2]=''
mainsql[3]=''

for num in 1 2 3;
do

mainsql[${num}]='--'

echo "
-- Put together manually.  by chacham
-- Could not see rep for all polls
-- Replies counted in different ways. Should really be double checked.
-- ANSI date format used to aid working in another RDBMS
-- 12/31 counted in following year.

with poll (posted, votes, replies, rep, id) as (
  -- cast values of (only) the first row to get datatypes right:
  select 
         cast('2013-12-31' as date   )
       , cast( 0367        as integer)
       , cast( 014         as integer)
       , cast( NULL        as integer)
       , cast(1068839      as integer)
                                                      union all

  select '2014-02-01', 0471, 014, NULL, 1072962  union all
  select '2014-03-01', 0617, 012,   15, 1076665  union all
  select '2014-04-01', 0685, 010,   21, 1080513  union all
  select '2014-05-01', 0790, 052,   13, 1084594  union all
  select '2014-06-01', 0641, 027,   18, 1088179  union all
  select '2014-07-01', 0304, 023,   13, 1091827  union all
  select '2014-07-16', 0261, 039,   15, 1093839  union all
  select '2014-08-01', 0381, 024,   19, 1095870  union all
  select '2014-08-16', 0300, 038,   19, 1097658  union all
  select '2014-09-01', 0158, 026,   05, 1099153  union all
  select '2014-09-16', 0395, 026,   16, 1100721  union all
  select '2014-10-01', 0385, 032,   16, 1102521  union all
  select '2014-10-16', 0230, 032,   12, 1104030  union all
  select '2014-11-01', 0260, 035,   21, 1105789  union all
  select '2014-11-17', 0214, 012,   19, 1107394  union all
  select '2014-12-01', 0406, 014,   14, 1108806  union all
  select '2014-12-17', 0244, 018,   16, 1110616  union all
  select '2015-01-01', 0306, 013,   13, 1111889  union all
  select '2015-01-16', 0263, 023,   25, 1113517  union all
  select '2015-02-01', 0472, 025,   21, 1115220  union all
  select '2015-03-01', 0681, 020,   15, 1118273  union all
  select '2015-04-01', 0579, 028,   14, 1122103  union all
  select '2015-05-01', 0610, 018,   10, 1125345  union all
  select '2015-06-01', 0406, 028,   11, 1128535  union all
  select '2003-12-31', 0381, 038, NULL,  317976  union all
  select '2004-01-10', 0554, 073, NULL,  320300  union all
  select '2004-01-31', 0493, 043, NULL,  325590  union all
  select '2004-02-13', 0231, 040, NULL,  328924  union all
  select '2004-02-20', 0528, 038, NULL,  330663  union all
  select '2004-03-14', 0674, 035, NULL,  336465  union all
  select '2004-03-28', 0447, 025, NULL,  340356  union all
  select '2004-04-06', 0685, 035, NULL,  343106  union all
  select '2004-04-27', 0354, 047, NULL,  348710  union all
  select '2004-05-06', 0239, 016, NULL,  351331  union all
  select '2004-05-09', 1134, 055, NULL,  351805  union all
  select '2004-05-27', 0750, 092, NULL,  357125  union all
  select '2004-06-16', 0676, 032, NULL,  367278  union all
  select '2004-07-14', 0847, 096, NULL,  374488  union all
  select '2004-08-02', 0331, 040, NULL,  379195  union all
  select '2004-08-16', 0721, 048, NULL,  383208  union all
  select '2004-08-31', 1208, 097, NULL,  387377  union all
  select '2004-09-27', 0627, 067, NULL,  394350  union all
  select '2004-10-19', 0570, 047, NULL,  400428  union all
  select '2004-11-02', 0384, 047, NULL,  404708  union all
  select '2004-11-08', 0719, 039, NULL,  405984  union all
  select '2004-11-17', 0603, 031, NULL,  408565  union all
  select '2004-12-15', 0385, 025, NULL,  415282  union all
  select '2004-12-31', 0516, 037, NULL,  418471  union all
  select '2005-01-15', 0975, 034, NULL,  422479  union all
  select '2005-02-15', 0941, 076, NULL,  431048  union all
  select '2005-03-16', 0702, 088, NULL,  439956  union all
  select '2005-04-09', 0359, 022, NULL,  446224  union all
  select '2005-04-23', 0304, 019, NULL,  450653  union all
  select '2005-05-05', 0526, 051, NULL,  454408  union all
  select '2005-05-27', 0383, 069, NULL,  461272  union all
  select '2005-06-08', 0919, 044, NULL,  464548  union all
  select '2005-07-12', 0462, 029, NULL,  474189  union all
  select '2005-07-21', 0152, 027, NULL,  476741  union all
  select '2005-07-27', 0275, 021, NULL,  478760  union all
  select '2005-08-04', 0293, 028, NULL,  480707  union all
  select '2005-08-10', 0288, 035, NULL,  482550  union all
  select '2005-08-17', 0249, 044, NULL,  484367  union all
  select '2005-08-24', 0194, 004, NULL,  486258  union all
  select '2005-09-06', 0141, 048, NULL,  489623  union all
  select '2005-09-08', 0294, 056, NULL,  490121  union all
  select '2005-09-13', 0427, 048, NULL,  491469  union all
  select '2005-09-22', 0270, 023, NULL,  494181  union all
  select '2005-09-29', 0478, 034, NULL,  496105  union all
  select '2005-10-09', 0248, 028, NULL,  498505  union all
  select '2005-10-12', 0560, 029, NULL,  499711  union all
  select '2005-10-30', 0122, 031, NULL,  504009  union all
  select '2005-11-01', 0096, 011, NULL,  504562  union all
  select '2005-11-02', 0310, 022, NULL,  504834  union all
  select '2005-11-08', 0205, 032, NULL,  506825  union all
  select '2005-11-16', 0646, 106, NULL,  509130  union all
  select '2005-12-06', 0358, 052, NULL,  514544  union all
  select '2005-12-14', 0291, 024, NULL,  516577  union all
  select '2005-12-20', 0263, 031, NULL,  518012  union all
  select '2005-12-26', 0293, 031, NULL,  519073
)
, split as (
     select
         posted
       ,
         cast(to_char(posted, 'YY')  as integer) + 
         cast(
            case
            when to_char(posted, 'MM') = '12' and to_char(posted, 'DD'
+) = '31'
            then  1
            else  0
            end 
         as integer) as Y
      , 
         case
         when to_char(posted, 'MM') = '12' AND TO_CHAR(posted, 'DD') =
+ '31'
         then    '01'
         else to_char(posted, 'MM')
         end 
                             as M
      ,  votes
      ,  replies
      , rep
      , id
    from
        poll
)

${mainsql[1]} /*
select
    Y,
    M,
    round(avg(votes))     as votes,
    round(avg(replies))   as replies
from split
group by rollup(Y, M)
order by Y, M
${mainsql[1]} */

${mainsql[2]} /*
select
    say.M,
    round(avg(say.votes)) votes,
    round(avg(case split.Y when 04 then  split.votes end))   V_04,
    round(avg(case split.Y when 05 then  split.votes end))   V_05,
    round(avg(case split.Y when 14 then  split.votes end))   V_14,
    round(avg(case split.Y when 15 then  split.votes end))   V_15,
    round(avg(say.replies))                                  replies,
    round(avg(case split.Y when 04 then  split.replies end)) R_04,
    round(avg(case split.Y when 05 then  split.replies end)) R_05,
    round(avg(case split.Y when 14 then  split.replies end)) R_14,
    round(avg(case split.Y when 15 then  split.replies end)) R_15
from
    split,
    split as say   -- split All Years
where say.m = split.M
group by say.M
order by say.M

${mainsql[2]} */

${mainsql[3]} /*

select
    Y,
    M,
    count(*) total
from split
group by rollup(Y, M)
order by Y, M

${mainsql[3]} */
;

" 
mainsql[${num}]=''

done | psql
[download]


 y  | m  | votes | replies
----+----+-------+---------
  4 | 01 |   476 |      51
  4 | 02 |   380 |      39
  4 | 03 |   561 |      30
  4 | 04 |   520 |      41
  4 | 05 |   708 |      54
  4 | 06 |   676 |      32
  4 | 07 |   847 |      96
  4 | 08 |   753 |      62
  4 | 09 |   627 |      67
  4 | 10 |   570 |      47
  4 | 11 |   569 |      39
  4 | 12 |   385 |      25
  4 |    |   589 |      48
  5 | 01 |   746 |      36
  5 | 02 |   941 |      76
  5 | 03 |   702 |      88
  5 | 04 |   332 |      21
  5 | 05 |   455 |      60
  5 | 06 |   919 |      44
  5 | 07 |   296 |      26
  5 | 08 |   256 |      28
  5 | 09 |   322 |      42
  5 | 10 |   310 |      29
  5 | 11 |   314 |      43
  5 | 12 |   301 |      35
  5 |    |   392 |      39
 14 | 01 |   367 |      14
 14 | 02 |   471 |      14
 14 | 03 |   617 |      12
 14 | 04 |   685 |      10
 14 | 05 |   790 |      52
 14 | 06 |   641 |      27
 14 | 07 |   283 |      31
 14 | 08 |   341 |      31
 14 | 09 |   277 |      26
 14 | 10 |   308 |      32
 14 | 11 |   237 |      24
 14 | 12 |   325 |      16
 14 |    |   395 |      25
 15 | 01 |   285 |      18
 15 | 02 |   472 |      25
 15 | 03 |   681 |      20
 15 | 04 |   579 |      28
 15 | 05 |   610 |      18
 15 | 06 |   406 |      28
 15 |    |   474 |      22
    |    |   456 |      37
(47 rows)

 m  | votes | v_04 | v_05 | v_14 | v_15 | replies | r_04 | r_05 | r_14
+ | r_15
----+-------+------+------+------+------+---------+------+------+-----
+-+------
 01 |   482 |  476 |  746 |  367 |  285 |      34 |   51 |   36 |   14
+ |   18
 02 |   529 |  380 |  941 |  471 |  472 |      39 |   39 |   76 |   14
+ |   25
 03 |   624 |  561 |  702 |  617 |  681 |      36 |   30 |   88 |   12
+ |   20
 04 |   494 |  520 |  332 |  685 |  579 |      27 |   41 |   21 |   10
+ |   28
 05 |   633 |  708 |  455 |  790 |  610 |      50 |   54 |   60 |   52
+ |   18
 06 |   661 |  676 |  919 |  641 |  406 |      33 |   32 |   44 |   27
+ |   28
 07 |   384 |  847 |  296 |  283 |      |      39 |   96 |   26 |   31
+ |
 08 |   441 |  753 |  256 |  341 |      |      40 |   62 |   28 |   31
+ |
 09 |   349 |  627 |  322 |  277 |      |      41 |   67 |   42 |   26
+ |
 10 |   353 |  570 |  310 |  308 |      |      33 |   47 |   29 |   32
+ |
 11 |   382 |  569 |  314 |  237 |      |      37 |   39 |   43 |   24
+ |
 12 |   320 |  385 |  301 |  325 |      |      28 |   25 |   35 |   16
+ |
(12 rows)

 y  | m  | total
----+----+-------
  4 | 01 |     3
  4 | 02 |     2
  4 | 03 |     2
  4 | 04 |     2
  4 | 05 |     3
  4 | 06 |     1
  4 | 07 |     1
  4 | 08 |     3
  4 | 09 |     1
  4 | 10 |     1
  4 | 11 |     3
  4 | 12 |     1
  4 |    |    23
  5 | 01 |     2
  5 | 02 |     1
  5 | 03 |     1
  5 | 04 |     2
  5 | 05 |     2
  5 | 06 |     1
  5 | 07 |     3
  5 | 08 |     4
  5 | 09 |     5
  5 | 10 |     3
  5 | 11 |     4
  5 | 12 |     4
  5 |    |    32
 14 | 01 |     1
 14 | 02 |     1
 14 | 03 |     1
 14 | 04 |     1
 14 | 05 |     1
 14 | 06 |     1
 14 | 07 |     2
 14 | 08 |     2
 14 | 09 |     2
 14 | 10 |     2
 14 | 11 |     2
 14 | 12 |     2
 14 |    |    18
 15 | 01 |     2
 15 | 02 |     1
 15 | 03 |     1
 15 | 04 |     1
 15 | 05 |     1
 15 | 06 |     1
 15 |    |     7
    |    |    80
(47 rows)
[download]

Comment on Re^3: Terminal decline? (Oracle-2-PostgreSQL port) Select or Download Code


Perl-Sensitive Sunglasses
	PerlMonks