Forum OpenACS Development: Crossing table rows to rows and columns
Hi there,
How would I convert/rotate (i.e. cross table) the following datasource, which is originally returned by rows (i.e. datetime and total), to rows as hours and columns as dates, where the columns (dates) will be assigned with "total" as their value.
Here it is the chunk of code to convert from base64url to binary PNG
select date_trunc('hour', o.creation_date) AS datetime,
COUNT(1) AS total
FROM cr_items ci, acs_objects o, cr_revisions cr
WHERE ci.item_id = o.object_id
AND ci.item_id = cr.item_id
AND ci.latest_revision = cr.revision_id
AND ci.content_type = :content_type
AND o.creation_date BETWEEN :creation_date::date - INTERVAL '6 day' AND :creation_date::date + INTERVAL '1 day'
GROUP BY 1 ORDER BY datetime ASC'' From the table structure, such as:
hour | total
------------------------+-------
2020-07-26 02:00:00+00 | 1
2020-07-26 04:00:00+00 | 7
2020-07-26 05:00:00+00 | 6
2020-07-26 06:00:00+00 | 6
2020-07-26 07:00:00+00 | 17
2020-07-26 08:00:00+00 | 17
2020-07-26 09:00:00+00 | 6
2020-07-26 10:00:00+00 | 8
2020-07-26 11:00:00+00 | 14
2020-07-26 12:00:00+00 | 16
2020-07-26 13:00:00+00 | 10
2020-07-26 14:00:00+00 | 17
2020-07-26 15:00:00+00 | 15
2020-07-26 16:00:00+00 | 2
2020-07-27 00:00:00+00 | 1
2020-07-27 06:00:00+00 | 1
...
2020-08-01 07:00:00+00 | 7
2020-08-01 08:00:00+00 | 4
2020-08-01 09:00:00+00 | 7
2020-08-01 10:00:00+00 | 10
2020-08-01 11:00:00+00 | 20
2020-08-01 12:00:00+00 | 25
2020-08-01 13:00:00+00 | 18
2020-08-01 14:00:00+00 | 14
2020-08-01 15:00:00+00 | 12
2020-08-01 16:00:00+00 | 4
(91 rows)It would result in the table, as in:
hour 2020-7-26 2020-7-27 ... 2020-7-31 2020-8-01
0:00:00
1:00:00
2:00:00
3:00:00
4:00:00
5:00:00 1
6:00:00 2 2 4 22 7 4
7:00:00 8 2 3 8 1
8:00:00 3 8 4 1 9 4
9:00:00 4 6 2 35 8
10:00:00 9 19 14 2 10 2
11:00:00 11 8 7 13 10 13 10
12:00:00 12 7 18 12 8 12 5
13:00:00 6 14 8 24 10 6 6
14:00:00 8 10 9 7 14 11 4
15:00:00 21 10 4 2 13 15
16:00:00 12 15 11 10 22 22
17:00:00 30 14 11 28 10 29
18:00:00 1
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00 I've tried to use crosstab(text sql), to solve the problem directly in the datasource layer, but apparently tablefunc is not supported in the OACS schema https://www.postgresql.org/docs/9.2/tablefunc.html
ERROR: function crosstab(unknown, unknown) does not exist
LINE 1: select * from crosstab('select o.creation_date::date AS day ...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.Furthermore, as the final output is a CSV file, which I was intended to use adproc template::list::writecsv, so I decided to give a try with template::list and db_multirow structures, but it resulted in a very complex and dirty source.
Starting from creating list elements dynamically, as in:
for {set i 0} {$i < 7} {incr i} {
set from [clock format [clock scan {+1 day} -base [clock scan $from]] -format "%Y%m%d"]
lappend elements $from {label ""}
append columns "$from "
}
ns_log Notice "ELEMENTS $elements"
ns_log notice "COLUMNS $columns"
template::list::create \
-name persons \
-multirow persons \
-key item_id \
-elements $elements... to the db_multirow, where the column "hour" is truncated to display only hour, and the date part must be converted in a column of the template::list.
db_multirow -extend { textinfo $columns} persons select_p $sql {
....
set date [lindex [split [lindex $hour 0] " "] 0]
set hour [lindex [split [lindex $hour 0] " "] 1]
...So confusing and cluttered, that I gave up to continue writing this garbage code, and decide to post this thread.
Thus, I was wondering if there is a better approach to write a beautiful code out of it, using api-doc.
Does anyone have an idea on how to write this crosstable display using OACS API?
Best wishes, I
I recommend installing tablefunc. It's available as a PostgreSQL contrib module. OpenACS doesn't have any specific requirements for it, but there's a lot of create stuff in there, if you are still using PostgreSQL 9.
Look into how your OS installed the PostgreSQL contrib packages. Then you can just create a SQL upgrade script to install it into your database.
I've used tablefunc with OpenACS projects since PostgreSQL 9 was new.
CREATE EXTENSION IF NOT EXISTS tablefunc;
If this works, your are done, you can use the crosstab family
oacs-5-10=# \df crosstab crosstab crosstab2 crosstab3 crosstab4
otherwise progress as Dave suggested
see also:
https://stackoverflow.com/questions/3002499/postgresql-crosstab-query
https://www.vertabelo.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/
