获取动态创建的透视表的行总数

Question

在PostgreSQL中，我有一个这样的表:

CREATE TABLE cross_table (brand varchar(10), gender varchar(10), sales int);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 30);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 40);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);

然后我运行这个查询以获取brand作为行，gender作为列，sales作为值:

with main_query as (    
SELECT  brand,
GROUPING(brand) AS "brand_grouping",
gender,
GROUPING(gender) AS "gender_grouping",
sum(sales) AS "sales"
FROM    cross_table
GROUP BY ROLLUP (brand, gender)
),  
second_query AS (
SELECT  brand,
brand_grouping,
cast(
json_object_agg(
gender,
sales
ORDER BY gender DESC
) FILTER (WHERE gender_grouping = 0) AS jsonb) "gender",
SUM(sales)  AS "sales"
FROM main_query
GROUP BY (brand, brand_grouping)
)
SELECT  brand,
gender,
sales
FROM    second_query
ORDER BY brand_grouping, brand

这将产生以下结果:

<表类>品牌性别销售tbody><<tr>阿迪达斯{"Woman" 70}140耐克"Male": 60120彪马"Male": 10"Woman" 50120空空190

试试这个:

SELECT brand
, jsonb_object_agg(gender, sales) AS gender
, sum(sales) AS sales
FROM (
SELECT brand
, gender
, sum(sales) AS sales
FROM cross_table
GROUP BY ROLLUP(brand), gender
) AS a
GROUP BY brand

结果:

<表类>品牌性别销售tbody><<tr>空{"Male" 70年,"Woman": 120}190阿迪达斯{"Woman" 70}70耐克{"Male" 60}60彪马{"Male" 10"Woman": 50}60