postgreSQL:隐藏计算列

Question

CREATE table orders 
{
id integer,
product_id integer,
type VARCHAR(16)
}
SELECT
(SELECT COUNT(*) FROM orders) AS "Order Count",
-- I don't want total to show up
(SELECT COUNT(*) FROM orders WHERE product_id = 500) AS "total",
(SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'small') * 100 / "total" AS "% Small Sold",
(SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'medium') * 100 / "total" AS "% Medium Sold",
(SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'large') * 100 / "total" AS "% Large Sold"
FROM 
orders

我有这个SQL报告。我有很多列，其中一个是用来计算其他列的，在这个例子中是"total"。但我不想让它出现在报告里。是否有一种方法可以在查询的其他部分进行编码或将其标记为隐藏?我正在使用Postgres。

Answer 1

您可以使用公共表表达式(CTE)来表示总数。然后从CTE中选择要保留在报告中的字段。

WITH totals AS (
SELECT
(SELECT COUNT(*) FROM orders) AS "Order Count",
-- I don't want total to show up
(SELECT COUNT(*) FROM orders WHERE product_id = 500) AS "total",
(SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'small') * 100 / "total" AS "% Small Sold",
(SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'medium') * 100 / "total" AS "% Medium Sold",
(SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'large') * 100 / "total" AS "% Large Sold"
FROM orders    
)
SELECT
"Order Count",
"% Small Sold",
"% Medium Sold",
"% Large Sold"
FROM
totals

Answer 2

我不确定你所说的隐藏是什么意思，但我必须向你展示一种更好的方式来编写这个查询

SELECT
COUNT(*) AS "Order Count",
-- I don't want total to show up
SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "total",
SUM(CASE WHEN PRODUCT_ID = 500 AND type = 'small' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "% Small Soldl",
SUM(CASE WHEN PRODUCT_ID = 500 AND type = 'medium' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "% Medium Soldl",
SUM(CASE WHEN PRODUCT_ID = 500 AND type = 'large' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "% Large Soldl",
FROM orders

我希望你能看到性能的显著提高

Answer 3

为什么不先过滤一下呢?这会降低您的查询性能。

检查:

with maintab as (select case
when type is not Null then type
else 'total'
end type, count(*) cnt from orders
where product_id = 500 and type in ('small', 'medium', 'large')
group by rollup(type))
select type, cnt*100/(select cnt from maintab where type = 'total' ) percentage
from maintab
where type in ('small', 'medium', 'large');

如果您的表在product_id和type列中包含不同的值，当然您应该使用这个:

with cnttab as (select count(*) cnt from orders), maintab as (select type, count(*) cnt from orders
where product_id = 500 and type in ('small', 'medium', 'large')
group by type)
select type, cnt*100/(select cnt from cnttab) percentage
from maintab;

你可以比较解释方案。