Postgresql – How to create a custom view (using lookup) from aggregated data

aggregateimportpostgresql

I have data imported into a postgresql database:

groups (data import)
id | csv_user_ids
1  | 1,2,3
2  | 4,5,6
3  | 3,1,4

… and a lookup table for user names:

users (lookup)
id | name
1  | one
2  | two
3  | three
4  | four
5  | five
6  | six

I need to build a VIEW of groups with JSON column matching the user IDs as such

groups_view
id | json_array_users
1  | [{ 'id': 1, 'name': 'one'}, { 'id': 2, 'name': 'two'}, { 'id': 3, 'name': 'three'}]
2  | [{ 'id': 4, 'name': 'four'}, { 'id': 5, 'name': 'five'}, { 'id': 6, 'name': 'six'}]
3  | [{ 'id': 3, 'name': 'three'}, { 'id': 1, 'name': 'one'}, { 'id': 4, 'name': 'four'}]

How do I do that?

I guess I'd need to disaggregate rows in groups before aggregating into my new view again?

JSON result is an array.

source:

groups (data import)
id | csv_user_ids
1  | 1,2,3
2  | 4,5,6
3  | 3,1,4

to:

group_user
group_id | user_id
1        | 1
1        | 2
1        | 3
2        | 4
2        | 5
2        | 6
3        | 3
3        | 1
3        | 6

How do I do that in SQL?

With string_to_array(text, text [, text]) from doc functions array, I was able to turn the CSV string into an array e.g. {1,2,3}

groups (data import)
id | csv_user_ids 
1  | 1,2,3        => {1,2,3}
2  | 4,5,6        => {4,5,6}
3  | 3,1,4        => {3,1,4}

after what I tried array_to_json(anyarray [, pretty_bool]) doc functions-json …

Best Answer

To disaggregate

SELECT id, 
   json_array_elements(array_to_json(string_to_array(csv_user_ids, ',')))
FROM groups

string_to_array converts the CSV string into ARRAY
array_to_json converts ARRAY into JSON
json_array_elements each element into a separate row

next I just need to aggregate again.

Based on comments

The query could probably be simpler / faster this way:

SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team1_kills -- team1
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team2_kills -- team2
   FROM   details d
   JOIN   players p ON p.id = d.player_slot  -- guessing from comment
   WHERE  p.team_id  IN (?, ?)               -- teams to compare go here.
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;

PostgreSQL – Return Total Number of Rows and Selected Aggregated Data

As I understand the question, you don't need a window function. Aggregate functions do the job:

count() in the lowest level (-> row_ct).
sum() the resulting row_ct in the next level (-> total_row_ct).

SELECT row_to_json(selected_records)::text AS data
FROM  (   
   SELECT array_to_json(array_agg(row_to_json(records))) AS data
        , sum(row_ct) AS total_row_ct
   FROM (
      SELECT landing_path_id
           , sum(entrances) AS entrances
           , count(*) AS row_ct
      FROM   report_la
      WHERE  profile_id = 3777614
      GROUP  BY landing_path_id
      LIMIT  10
      ) records
   ) selected_records;

I also included landing_path_id so the resulting data makes sense.

SQL Fiddle.

Window function?

A window function (count(*) over ()) does not seem to be what you want, since you don't have unaggregated rows.
You could add to the inner subquery:

count(*) OVER ()

.. to get the count of distinct landing_path_id, which is one other possible number that might be of interest. But that doesn't seem to be what you meant by "the total number of rows from that records select".
Or you could add to the inner subquery:

sum(count(*)) OVER ()

.. to get the total count with every landing_path_id redundantly, but that would seem pointless. Just mentioning that to demonstrate it's possible to run a window function over the result of an aggregate function in a single pass. Details for that:

Updated question

Your result, just without total_count in the records subquery. Now accounting for the LIMIT in the inner SELECT. Even though a maximum of 10 distinct landing_path_id is selected, all qualifying landing_path_id are counted.

To get both in one scan and reuse count and sum separately I introduce a CTE:

WITH cte AS (
  SELECT sum(entrances) AS entrances
       , count(*) over () AS total_count
  FROM   report_la
  WHERE  profile_id = 3777614
  GROUP  BY landing_path_id
  LIMIT  10
  )
SELECT row_to_json(selected_records)::text AS data
FROM  (   
   SELECT (SELECT total_count FROM cte LIMIT 1) AS total_count
        , array_to_json(array_agg(row_to_json(records))) AS data
   FROM  (SELECT entrances FROM cte) records
   ) selected_records;

If you don't care about the attribute name, you can have that cheaper with a subquery:

SELECT row_to_json(selected_records)::text AS data
FROM  (   
   SELECT min(total_count) AS total_count
        , array_to_json(array_agg(row_to_json(ROW(entrances)))) AS data
   FROM (
      SELECT sum(entrances) AS entrances
           , count(*) over () AS total_count  -- shouldn't show up in result
      FROM   report_la
      WHERE  profile_id = 3777614
      GROUP  BY landing_path_id
      LIMIT  1
      ) records
   ) selected_records;

You get the default attribute name f1 instead of entrances, since the ROW expression does not preserve the column name.

If you need a certain attribute name, you could cast the row to a registered type. (Ab-)using a TEMP TABLE to register my row type for the session:

CREATE TEMP TABLE rec1 (entrances bigint);

...
        , array_to_json(array_agg(row_to_json(ROW(entrances)::rec1))) AS data
...

This would be a bit faster than the CTE. Or, more verbose but without cast:

...
        , array_to_json(array_agg(row_to_json(
                   (SELECT x FROM (SELECT records.entrances) x)))) AS data
...

Detailed explanation in this related answer:

Select columns inside json_agg