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 報告。我有許多列，其中一個我正在創建用于計算我的其他列，在本例中為“總計”。我不希望它出現在報告中。有沒有辦法在查詢的其他部分對其進行編碼或將其標記為隱藏？我正在使用 Postgres。
uj5u.com熱心網友回復：
您可以對總計使用公用表運算式 (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
uj5u.com熱心網友回復：
我不確定您所說的隱藏是什么意思，但我必須向您展示撰寫此查詢的更好方法
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
我希望您會看到性能顯著提高
uj5u.com熱心網友回復：
為什么不先過濾？它正在降低您的查詢性能。
檢查這個：
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;
您可以比較解釋計劃。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/484631.html
標籤：sql PostgreSQL
上一篇：如何為基于復合型別的PostgresDOMAIN定義CHECK約束
下一篇：在存盤程序中實作咨詢鎖