I have a list of 191 values that I want to compare to a column. I ultimately want to get a count of % of rows that have a value in my master list (matches/(non-matches + NULL)).
I know I could do something like below but am wondering if this is the most efficient way? Is it possible to create an array that stores the values and check against this? Not sure what the best practice way of doing this is given I have 191 values to check against?
I am hoping to avoid dropping 191 csv into the argument since this is a hit to formatting/readability. Is there a way to store these values inside an array or temp table so i can just drop a short hand variable into the actual query? Or is using the below method/average method still the best way to go regardless of how many values there are to check against?
SELECT
SUM(CASE WHEN COALESCE(field, '') IN (COMMA SEPARATED VALUES) THEN 1 ELSE 0 END) as matches,
COUNT(COALESCE(field)) as total_rows
FROM table
Also, I believe that COUNT(*) and COUNT(1) are blind to NULL fields so can anyone confirm if using COUNT(COALESCE(FIELD)) ensures the count includes null values from the field?
Martin Traverso :
Presto doesn't support temporary tables, but you could improve readability of your query by using an inline table (WITH clause combined with VALUES) to avoid having a long list of values in the aggregation expression.\n\nThen, you can count the number of matches by doing the following. Note the use of FILTER for improved readability.\n\ncount(*) FILTER (WHERE field IN (SELECT value FROM data))\n\n\nHere's a complete example:\n\nWITH data(value) as (VALUES\n 'value1',\n 'value2',\n ...\n)\nSELECT\n count(*) AS total_rows,\n count(*) FILTER (WHERE field IN (SELECT value FROM data)) AS matches\nFROM t\n",
2020-05-21T01:45:15
Gordon Linoff :
I think you just want:\n\nSELECT AVG(CASE WHEN field IN (COMMA SEPARATED VALUES) THEN 1.0 ELSE 0 END) as match_ratio\nFROM table\n",
2020-05-21T01:15:03