How to get rid of VIEW in this request-CodePudding

CREATE VIEW A1 AS
SELECT client_ID , COUNT(dog_id)
FROM test_clients
GROUP BY client_ID
HAVING COUNT(dog_id)=2;

CREATE VIEW A2 AS
SELECT filial , COUNT(A1.client_ID)
FROM A1
JOIN test_clients USING (client_ID)
GROUP BY filial
HAVING COUNT(A1.client_ID)>10;

SELECT COUNT(filial)
FROM A2;

As far as I understand, this can be done through a subquery, but how?

CodePudding user response：

Burns down to:

SELECT count(*)
FROM  (
   SELECT 1
   FROM  (
      SELECT client_id
      FROM   test_clients
      GROUP  BY 1
      HAVING count(dog_id) = 2
      ) a1
   JOIN   test_clients USING (client_id)
   GROUP  BY filial
   HAVING count(*) > 10
   ) a2;

Assuming filial is defined NOT NULL.

Probably faster to use a window function and get rid of the self-join:

SELECT count(*)
FROM  (
   SELECT 1
   FROM  (
      SELECT filial
           , count(dog_id) OVER (PARTITION BY client_id) AS dog_ct
      FROM   test_clients
      ) a1
   WHERE  dog_ct = 2
   GROUP  BY filial
   HAVING count(*) > 10
   ) a2;

Depending on your exact table definition we might be able to optimize a bit further ...

CodePudding user response：

A slight refractor of Erwin's suggestion, just for you to play around with...

The outer query works because...

the inner query happens first
the WHERE clause happens next
then the GROUP BY and HAVING clauses
then the SELECT clause (so the COUNT() OVER ())
finally the DISTINCT

SELECT
  DISTINCT
  COUNT(filial) OVER ()
FROM
(
  SELECT
    filial,
    client_id,
    COUNT(dog_id) OVER (PARTITION BY client_id) AS client_dog_ct
  FROM
    test_clients
)
  count_dogs
WHERE
  client_dog_ct = 2
GROUP BY
  filial
HAVING
  COUNT(DISTINCT client_id) > 10

You may or may not want the COUNT(DISTINCT client_id), its not clear. So, play with that too.

I'm not saying it's any better, just that it's different and might help your learning.