Query to get count of distinct items in groupings

user_12928378 0
Viewed 348

I have a table that stores created grouping for items from another table like this:

table1 table1

table2 table2

So giving the above, I want to write a query that returns the count of items from table1 that a grouping has been created for.

It may sound like doing the below but that is actually not what I'm looking for because the groups have to be manually created for them to appear in table 2 so you may have an item from table1 that does't exist in table 2 because the grouping hasn't been created (i.e id: 555).

SELECT count(id)
FROM table1
WHERE group IS NOT NULL

The above will return 4 but I need something that looks at table2 and returns 3 which is count of items from table1 whose group exists in the category column of table2.

My real table for this can be pretty large up to 100k+ rows so I don't think it is efficient to check if group string from table1 it exists in table2 one by one as that would probably take forever to run - or is that the only viable solution?

PS: tried to use table markdown but I must have screwed up somehow PPS categories column is not of json type, its just string

trino presto sql
2 Answers

Not sure that this will be faster but you can prepare an existing categories aggregate. Something like that (also you can try set_union instead of array_agg with flatten and array_distinct):

SELECT array_distinct(flatten(array_agg(CAST(JSON_EXTRACT(categories, '$.x') as ARRAY(VARCHAR)))))
FROM table2

And check that group is in the result.

user_25012790

Assuming that table2 would not contain any groups in the array that are not there in table1, you can try the following:

WITH table1(id, "group", qty) AS (
    SELECT *
    FROM (VALUES (111, 'cups', 1),
                 (222, 'plates', 2),
                 (333, 'spoons', 5),
                 (444, null, 2),
                 (555, 'knives', 2))
),
     table2(group_id, categories, count_inventory) as (
         SELECT *
         FROM (VALUES ('A1', CAST(MAP(ARRAY['x'], ARRAY[ARRAY['cups', 'plates']]) AS JSON), 3),
                      ('B1', CAST(MAP(ARRAY['x'], ARRAY[ARRAY['cups']]) AS JSON), 1),
                      ('C1', CAST(MAP(ARRAY['x'], ARRAY[ARRAY['cups', 'spoons']]) AS JSON), 6),
                      ('C4', CAST(MAP(ARRAY['x'], ARRAY[ARRAY['spoons']]) AS JSON), 5)
              ))
SELECT reduce(
    array_agg(CAST(json_extract(categories, '$.x') AS ARRAY(VARCHAR))),
    array[],
    (s, x) -> array_union(s, x),
    x -> cardinality(x)
    )
FROM table2 WHERE categories is not null;
user_84916420
Related