SQL Select from table using the value of a previous SELECT query?-CodePudding

I implemented following query. it is worked, but an execution is long, because table has more than 400 000 records.

select count (*),
       count (*) filter ( where condition_one = ? ),
       count (*) filter ( where condition_two = ?),
       count (*) filter ( where condition_three = ?  ),
       count (*) filter ( where condition_four is ? )
from table_name
where id = ANY ( ARRAY [ [ ? ] ] :: int [ ] )

I want to select count(*) and then using this count value select another counts with the condition.

CodePudding user response：

We can use case to assign 1 to values that we want to count.
We can also assign different values according to different conditions and use sum instead of count.

Select 
  Count(*) as "total",
  Count(case when condition1
             Then 1 end) as "count1",
  Sum(case when condition2.1
             Then 1 
           when condition2.2
              Then 2
           end) as "sum2"
From
...tables,joins, sub-queries etc

CodePudding user response：

I don't know if this will improve your query execution speed, but you can try combining with and subquerys.

Create index for columns condition_one, condition_two, condition_three and condition_four will help.

with tn as (
    select condition_one, condition_two, condition_three, condition_four
    from table_name where id = ANY ( ARRAY [ [ ? ] ] :: int [ ] )
)

select 
    (select count (*) from tn),
    (select count (*) from tn where condition_one = ?),
    (select count (*) from tn where condition_two = ?),
    (select count (*) from tn where condition_three = ?),
    (select count (*) from tn where condition_four = ?)

CodePudding user response：

I did the below for a table that has nearly 4 million records and the run time was less than 2 mins.

WITH my_count AS
     (   SELECT an_id, MAX(column1) AS column1, MAX(column2) AS column2
         FROM (SELECT DISTINCT an_id
                               ,CASE WHEN column1 = 1 THEN 1 ELSE 0 END AS column1
                               ,CASE WHEN column2 = 1 THEN 1 ELSE 0 END AS column2
               FROM table_name
               GROUP BY an_id)
      )
SELECT
  an_id
  , COUNT (DISTINCT CASE WHEN column1 = 1 THEN an_id END) AS column1
  , COUNT (DISTINCT CASE WHEN column2 = 1 THEN an_id END) AS column2
 FROM my_count
 GROUP BY an_id
 ORDER BY an_id