in reply to Re^2: Terminal decline?
in thread Terminal decline?

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]

Replies are listed 'Best First'.
Re^4: Terminal decline? (Oracle-2-PostgreSQL port)
by chacham (Prior) on Jun 16, 2015 at 12:24 UTC

    Yeah, using Oracle here. The DATE 'YYYY-MM-DD' should be compatible, and the date part extraction was separated into a second CTE, for ease of modification. Although i used Dual, a CTE named Dual can be added on top to include one record and make it all work elsewhere (and in Oracle too.)

    ROLLUP is great for reporting or (less frequently) generating records. I tried CUBE, but without a serious study, the month totals seemed better done in the second query, in line. FWIW, i was too lazy to (re-learn and) write a PIVOT query. :)

    Unfortunately, i did not have enough polls with rep to check the correlation between rep and posts. Though, that avenue looks promising.

    It'd be a lot of fun to write these queries against the actual database. That'd not only remove clerical errors, but be on all the data. By year, by month, and so on.

    There is one other effect, perhaps (it's just a guess), worth looking into. That is, if polls are changed often, does that cause subsequent polls to have a higher rating? That is, if they are changed often, people might anticipate them, and thus be more excited when one shows up. That excitement could translate into more posts, or so goes my hypothesis. If true, the quality of the poll would be of lesser significance than the frequency, vis-a-vis the popularity of a poll.

[reply]

In Section Perl Monks Discussion