问题

需要一些SQL帮助。

我有一张表，我正在尝试获取在线销售和POS销售之间的百分比分配。以下是我编写的查询，屏幕截图是我得到的内容，但我不知道如何继续。

我需要一个第三列，显示在线销售和POS销售之间的百分比分配。
这将是（POS/（POS+ONLINE））*100 和（ONLINE/（POS+ONLINE））*100

以下是我的当前查询

SELECT
CASE
    WHEN saleschannel = 1 THEN 'ONLINE'
    WHEN saleschannel = 6 THEN 'ONLINE'
    WHEN saleschannel = 7 THEN 'ONLINE'
    WHEN saleschannel = 8 THEN 'ONLINE'
    ELSE 'POS'
END AS CHANNEL, 
COUNT(*) AS TICKET_QTY
FROM performancesales 
WHERE salesdate BETWEEN '2022-08-01' AND '2023-01-31'
AND saleschannel IN ('1','6', '7', '8', '14')
GROUP BY CHANNEL 
ORDER BY 2;

谢谢Aaron，他的查询是正确的，对我很有帮助。我已经重写了它，使它更快。这是我的最终代码

SELECT
    channel,
    COUNT(*) AS ticket_qty,
    CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER() AS DECIMAL(10,2)) AS percentage_split
FROM (
    SELECT 
        CASE 
            WHEN saleschannel IN ('2','5','7') THEN 'ONLINE'
            WHEN saleschannel IN ('0','1','4','6','8') THEN 'POS'
            ELSE 'OTHER - (AGGREGATOR)'
        END AS channel, 
        1 AS ticket
    FROM performancesales 
    WHERE 
        salesdate BETWEEN '2022-08-01' AND '2023-01-31' 
        AND saleschannel IN ('0','1','2','3','4','5','6','7','8')
) t
GROUP BY channel;

希望对你有所帮助。

SELECT
CASE
    WHEN saleschannel = 1 THEN 'ONLINE'
    WHEN saleschannel = 6 THEN 'ONLINE'
    WHEN saleschannel = 7 THEN 'ONLINE'
    WHEN saleschannel = 8 THEN 'ONLINE'
    ELSE 'POS'
END AS CHANNEL, 
COUNT(*) AS TICKET_QTY
FROM performancesales 
WHERE salesdate BETWEEN '2022-08-01' AND '2023-01-31'
AND saleschannel IN ('1','6', '7', '8', '14')
GROUP BY CHANNEL 
ORDER BY 2;

SELECT
    channel,
    COUNT(*) AS ticket_qty,
    CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER() AS DECIMAL(10,2)) AS percentage_split
FROM (
    SELECT 
        CASE 
            WHEN saleschannel IN ('2','5','7') THEN 'ONLINE'
            WHEN saleschannel IN ('0','1','4','6','8') THEN 'POS'
            ELSE 'OTHER - (AGGREGATOR)'
        END AS channel, 
        1 AS ticket
    FROM performancesales 
    WHERE 
        salesdate BETWEEN '2022-08-01' AND '2023-01-31' 
        AND saleschannel IN ('0','1','2','3','4','5','6','7','8')
) t
GROUP BY channel;

答案1

得分: 1

WITH CTE AS
(
SELECT
    CASE
        WHEN saleschannel = 1 THEN 'ONLINE'
        WHEN saleschannel = 6 THEN 'ONLINE'
        WHEN saleschannel = 7 THEN 'ONLINE'
        WHEN saleschannel = 8 THEN 'ONLINE'
        ELSE 'POS'
    END AS CHANNEL,
    COUNT(*) AS TICKET_QTY
FROM
    performancesales
WHERE
    salesdate BETWEEN '2022-08-01' AND '2023-01-31'
    AND
    saleschannel IN ('1', '6', '7', '8', '14')
GROUP BY
    CHANNEL
)

SELECT
    CHANNEL,
    TICKET_QTY,
    CAST(TICKET_QTY AS DECIMAL(10,2)) / SUM(TICKET_QTY) OVER() AS [Percentage]
FROM
    CTE

将原始查询包装为CTE（共同表达式），可以根据需要随意命名它。SUM() OVER() 将为返回的每一行计算数据集的总和。您还必须将其中一个值转换为小数，否则将会将整数除以整数，从而得到整数结果。如果需要更多精度，请使用 DECIMAL（10,6）。

<details>
<summary>英文:</summary>

I thnk this will do it,
```SQL
WITH CTE AS 
(
SELECT
			CASE
				WHEN saleschannel = 1 THEN &#39;ONLINE&#39;
				WHEN saleschannel = 6 THEN &#39;ONLINE&#39;
				WHEN saleschannel = 7 THEN &#39;ONLINE&#39;
				WHEN saleschannel = 8 THEN &#39;ONLINE&#39;
				ELSE &#39;POS&#39;
			END AS CHANNEL, 
			COUNT(*) AS TICKET_QTY
FROM 
			performancesales 
WHERE 
			salesdate BETWEEN &#39;2022-08-01&#39; AND &#39;2023-01-31&#39;
		AND 
			saleschannel IN (&#39;1&#39;,&#39;6&#39;, &#39;7&#39;, &#39;8&#39;, &#39;14&#39;)
GROUP BY 
			CHANNEL 
)

SELECT
			CHANNEL
			,TICKET_QTY
			,CAST(TICKET_QTY AS DECIMAL(10,2)) / SUM(TICKET_QTY) OVER() AS [Percentage]
FROM
			CTE

Wrap the original query up as a CTE (Common Table Expression) [you can call it whatever you like]. the SUM()OVER() will calculate the total sum of the dataset for each row returned. You also have to convert one of the values to a decimal otherwise you will be dividing and integer by an integer and will get an integer result. If you need more precision, do DECIMAL (10,6)

答案2

得分: 0

你可以使用过滤聚合来完成这个任务。

select 
    count(*) filter (where saleschannel in ('1','6', '7', '8')) as pos_qty,
    count(*) filter (where saleschannel = '14') as online_qty,
    count(*) filter (where saleschannel in ('1','6', '7', '8'))::numeric / count(*) * 100 as pos_percentage,
    count(*) filter (where saleschannel = '14')::numeric / count(*) * 100 as online_percentage
FROM performancesales 
WHERE salesdate BETWEEN '2022-08-01' AND '2023-01-31'
  AND saleschannel IN ('1','6', '7', '8', '14');

select 
    count(*) filter (where saleschannel in ('1','6', '7', '8')) as pos_qty,
    count(*) filter (where saleschannel = '14') as online_qty,
    count(*) filter (where saleschannel in ('1','6', '7', '8'))::numeric / count(*) * 100 as pos_percentage,
    count(*) filter (where saleschannel = '14')::numeric / count(*) * 100 as online_percentage
FROM performancesales 
WHERE salesdate BETWEEN '2022-08-01' AND '2023-01-31'
  AND saleschannel IN ('1','6', '7', '8', '14');



</details>