By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
-- bare minimum table
CREATE TABLE surveys (
surveyid int PRIMARY KEY
, descriptor text
, accuracy_of_bill_yn text -- should be boolean
, cleanliness_yn text
, service_yn text
, value_yn text
);
INSERT INTO surveys VALUES
(1, 'Fast Food', 'Yes', 'Yes', 'No' , 'No')
, (2, 'Boutique' , 'No' , 'No' , 'Yes', 'Yes')
, (3, 'Bar' , 'No' , 'Yes', 'Yes', 'No')
, (4, 'Eatery' , 'No' , 'Yes', 'No' , 'No')
, (5, 'Bar' , 'No' , 'Yes', 'Yes', 'Yes') -- excluded below
;
CREATE TABLE excluded (
surveyid int PRIMARY KEY
);
INSERT INTO excluded VALUES
(5)
;
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 1
-- base query
SELECT CASE descriptor
WHEN 'Fast Food' THEN 'Base'
WHEN 'Sit Down' THEN 'Premium'
WHEN 'Bar' THEN 'Premium'
WHEN 'Eatery' THEN 'Premium'
WHEN 'Full Service' THEN 'Deluxe'
WHEN 'Boutique' THEN 'Deluxe'
END AS brand_group
, count(*) FILTER (WHERE accuracy_of_bill_yn = 'Yes') AS a_ct
, count(*) FILTER (WHERE cleanliness_yn = 'Yes') AS c_ct
, count(*) FILTER (WHERE service_yn = 'Yes') AS s_ct
, count(*) FILTER (WHERE value_yn = 'Yes') AS v_ct
FROM surveys s
WHERE NOT EXISTS (SELECT FROM excluded e WHERE e.surveyid = s.surveyid)
/* -- not in my minimum demo
AND responsedate >= now() - interval '12 months'
AND region IN (1,2,3,4,5)
AND brand_group IS NOT NULL
*/
GROUP BY 1;
| brand_group | a_ct | c_ct | s_ct | v_ct |
|---|---|---|---|---|
| Base | 1 | 1 | 0 | 0 |
| Deluxe | 0 | 0 | 1 | 1 |
| Premium | 0 | 2 | 1 | 0 |
SELECT 3
-- "unpivoted"
SELECT brand_group, p.*
FROM (
SELECT CASE descriptor
WHEN 'Fast Food' THEN 'Base'
WHEN 'Sit Down' THEN 'Premium'
WHEN 'Bar' THEN 'Premium'
WHEN 'Eatery' THEN 'Premium'
WHEN 'Full Service' THEN 'Deluxe'
WHEN 'Boutique' THEN 'Deluxe'
END AS brand_group
, count(*) FILTER (WHERE accuracy_of_bill_yn = 'Yes') AS a_ct
, count(*) FILTER (WHERE cleanliness_yn = 'Yes') AS c_ct
, count(*) FILTER (WHERE service_yn = 'Yes') AS s_ct
, count(*) FILTER (WHERE value_yn = 'Yes') AS v_ct
FROM surveys s
WHERE NOT EXISTS (SELECT FROM excluded e WHERE e.surveyid = s.surveyid)
/* -- not in my minimum demo
AND responsedate >= now() - interval '12 months'
-- AND surveyid NOT IN (SELECT surveyid FROM surveys) -- ?? nonsense
AND region IN (1,2,3,4,5)
AND brand_group IS NOT NULL
*/
GROUP BY 1
) sub
CROSS JOIN LATERAL (
VALUES
('Accuracy of bill', a_ct)
, ('Cleanliness' , c_ct)
, ('Service' , s_ct)
, ('Value' , v_ct)
) p(base, count)
ORDER BY 1, 2; -- need that?
| brand_group | base | count |
|---|---|---|
| Base | Accuracy of bill | 1 |
| Base | Cleanliness | 1 |
| Base | Service | 0 |
| Base | Value | 0 |
| Deluxe | Accuracy of bill | 0 |
| Deluxe | Cleanliness | 0 |
| Deluxe | Service | 1 |
| Deluxe | Value | 1 |
| Premium | Accuracy of bill | 0 |
| Premium | Cleanliness | 2 |
| Premium | Service | 1 |
| Premium | Value | 0 |
SELECT 12