Pivot Tables

The psql \crosstab command provides a quick way to reformat the output of an interactive query, but is of no use for applications.

The crosstab functions are awkward to use since they take text as an input.

JSON Aggregates

WITH results AS (
SELECT
  volume_name AS group_by,
  mtime_age_age || ' (mtime_age)' AS "age_bin",
  round(physical_size::numeric / power(2.0, 30), 1) AS "size physical (GiB)",
  count AS file_count,
  round(cost::numeric, 5) AS cost
FROM
  sf_reports.last_time_generic_current
ORDER BY group_by
)
SELECT group_by, jsonb_object_agg(age_bin, file_count)
FROM results
GROUP BY group_by

Left JOIN

WITH uid_mapping AS (
  SELECT uid, name
  FROM uid_mapping
),
bin1 AS (
  SELECT uid, count(f.id), sum(f.size) AS size
  FROM file_current f
  AND now() - f.mtime < '30 days'
  GROUP BY uid
),
bin2 AS (
  SELECT uid, count(f.id), sum(f.size) AS size
  FROM file_current f
  AND now() - f.mtime BETWEEN '30 days' AND '90 days'
  GROUP BY uid
),
bin3 AS (
  SELECT uid, count(f.id), sum(f.size) AS size
  FROM file_current f
  AND now() - f.mtime BETWEEN '90 days' AND '180 days'
  GROUP BY uid
),
SELECT uid_mapping.uid, uid_mapping.name,
  COALESCE(bin1.count, 0) + COALESCE(bin2.count, 0) + COALESCE(bin3.count, 0) AS "file count",
  COALESCE(bin1.size, 0) + COALESCE(bin2.size, 0) + COALESCE(bin3.count, 0) AS "total size",
  bin1.size AS "< 30 days",
  bin2.size AS "30-90 days",
  bin3.size AS "90-180 days",
FROM uid_mapping
LEFT JOIN bin1 ON (uid_mapping.uid=bin1.uid)
LEFT JOIN bin2 ON (uid_mapping.uid=bin2.uid)
LEFT JOIN bin3 ON (uid_mapping.uid=bin3.uid)
ORDER BY "total size" DESC