I would like to know how experienced sql users would compute a lot of counts with different conditions. I have a table [population] and a table [sql_rules].

My [population] table looks something like this with ~1.7M rows and ~30 columns.

My [sql_rules] table looks something like this, with ~800 rows and at maximum 8 rules (average ~5) :

What I need to do is basically to 'iterate' by row and to compute the count when applying the rules one after the other to obtain one 'count' by cell of my sql_rules table. Basically, since it is hard to explain with words, here are the count I would like to obtain :

What I did for now was to use my table [sql_rules] to create all the SQL 'queries'. Since they can often be similar until the third or forth column, I avoided recomputing the same count multiple times and took the values if a particular 'count' had already been computed.

Can someone think of a fastest way to do this ? My solution is working fine but I can't think of a faster way to do this. I'm able to work with sql, Python, R.

Edit : Ideally, what I'm looking for is some nice ideas that could potentially accelerate the process, for the sake of 'interest'. Here are examples of ideas that are not meant to be great, but to exemplify what I am looking for :

1. Using the sql_rules table, let's say with (~800 rows * ~5 rules in average), 4000 rules, create the 4000 sql queries and run them all one after the other until it's done.
2. Create the 4000 'queries' as above but once it is done, keep only the unique/distinct rules which would maybe reduce the count to 1000 'SELECT COUNT(*) FROM... .
3. loop over each line, create a duplicate of the population table and remove from the duplicate table the rows where the 'rules' are not met. That way, each condition/query/rules of the rest of the row will be computed on a smaller and smaller table.

Edit -------------------------------------

• The table is unfortunately proprietary but I will create something equivalent using public data as soon as possible.
• The sql_rules table is ~800rows and 8 columns (rules). Most of the time, one single row does not contain 8 rules and the a single row is thus looking like that :

with the values I need to find for this particular row being equivalent to

CodePudding user response：

You can use conditional sum , still query must be build for each set of rules, but where part can be easily implemented.

select 
  sum(1) filter (where hair_color = 'brown') rule1,
  sum(1) filter (where age < 27) rule2,
  sum(1) filter (where origin in ('US','UK')) rule3
 from population

DB Fiddle