将同一列中的2个值除以条件的函数是什么?

Question

我想返回我的总收益除以同一列下的总销售额

select
(select count(order_type_id) from ods_emea_all.order_emea 
where order_type_id in ('Return', 'RETURN')
and brand_cd =('PB')
and iso_country_cd IN ('IT', 'ES', 'GB', 'FR', 'DE'))*100/
(select count(order_type_id )
from ods_emea_all.order_emea 
where order_type_id in ('Sale','SALE')
and brand_cd =('PB')
and iso_country_cd IN ('IT', 'ES', 'GB', 'FR', 'DE'))
AS brand_return
from ods_emea_all.order_emea

Answer 1

如果您有以下包含内容的表:

CREATE TABLE order_emea (
order_type_id VARCHAR(100),
brand_cd VARCHAR(100),
iso_country_cd VARCHAR(100)
);
INSERT INTO order_emea VALUES
('Sale', 'PB', 'IT'),
('SALE', 'PB', 'FR'),
('Sale', 'PB', 'IT'),
('sale', 'PB', 'ES'),
('SALe', 'PB', 'ES'),
('sAle', 'PB', 'GB'),
('saLe', 'PB', 'FR'),
('sale', 'PB', 'DE'),
('Sale', 'PB', 'DE'),
('sale', 'PB', 'FR'),
('Return', 'PB', 'FR'),
('RETURN', 'PB', 'FR'),
('return', 'PB', 'GB'),
('REturn', 'PB', 'IT'),
('rEturn', 'PB', 'IT');

下面的查询将得到您想要的:

WITH base_table_n_returns_to_n_sales AS (
SELECT
SUM(CASE WHEN
LOWER(order_type_id) = 'return'
AND LOWER(brand_cd) = 'pb'
AND LOWER(iso_country_cd) IN ('IT', 'ES', 'GB', 'FR', 'DE')
THEN 1 ELSE 0 END) AS n_returns,
SUM(CASE WHEN
LOWER(order_type_id) = 'sale'
AND LOWER(brand_cd) = 'pb'
AND LOWER(iso_country_cd) IN ('IT', 'ES', 'GB', 'FR', 'DE')
THEN 1 ELSE 0 END) AS n_sales
FROM order_emea
)
SELECT
*,
n_returns / CAST(NULLIF(n_sales, 0) AS FLOAT) AS ratio_n_returns_to_n_sales
FROM base_table_n_returns_to_n_sales

看这提琴