如何结合SELECT语句以允许我计算SQL Server中的百分比，成功和失败？

| 想象一张桌子： CUST_PROMO（customer_id，PROMOTION），用作客户收到的每个促销之间的映射。

select promotion, count(customer_id) as promo_size
from CUST_PROMO
group by promotion

这样可以使我们获得每次促销的客户总数。现在，我们有了CUSTOMER（customer_id，PROMO_RESPONDED，PROMO_PURCHASED），其中列出了客户，促销活动使客户做出回应，以及购买了客户。

select PROMO_RESPONDED, count(customer_id) as promo_responded 
from CUSTOMER
group by PROMO_RESPONDED

select PROMO_PURCHASED,count(customer_id) as promo_responded 
from CUSTOMER 
group by PROMO_PURCHASED

这一切都是很不言自明的；现在，我已经获得了每个促销成功的人数。但;我想要的最终结果是[CSV格式]

PROMOTION,PROMO_SIZE,PROMO_RESPONDED,PROMO_PURCHASED,PROMO_RESPSUCCESSRATE,blah

1,100,12,5,12%,...
2,200,23,14,11.5%,...

我不知道该怎么做。我可以联合上面的三个查询；但这实际上并没有达到我想要的结果。我考虑过要创建一个内存表，在每个促销值中插入一个表，然后对它执行一个带有连接的update语句来设置每个值-但这很杂乱；并且每个表/选择语句都需要一个新的UPDATE语句。我还可以为每个结果集创建一个临时表，然后将它们连接在一起。但是真的谁想这样做？我想不出任何可行的方法来连接这些数据。因为我正在处理聚合。因此，充其量，我需要一个像UNION这样的函数，它将结合结果集，但实际上将像键上的列一样合并并添加这些列，而不是添加会增加行的并集。该描述听起来像是JOIN；但我看不到有效果。谢谢您的帮助！

已邀请:

4 个回复

递劝臼类洪

SELECT
  cp.promotion,
  PROMO_SIZE = COUNT(*),
  PROMO_RESPONDED = COUNT(c1.customer_id),
  PROMO_PURCHASED = COUNT(c2.customer_id),
  PROMO_RESPSUCCESSRATE = COUNT(c1.customer_id) * 100.0 / COUNT(*)
FROM CUST_PROMO cp
  LEFT JOIN CUSTOMER c1
    ON cp.customer_id = c1.customer_id AND cp.promotion = c1.PROMO_RESPONDED
  LEFT JOIN CUSTOMER c2
    ON cp.customer_id = c2.customer_id AND cp.promotion = c2.PROMO_PURCHASED
GROUP BY cp.promotion

泻伴墓荒

WITH tmp AS
(
    SELECT  PROMOTION, 0 as promo_responded, 0 as promo_purchased, COUNT(customer_id) as total
    FROM    CUST_PROMO
    GROUP BY PROMOTION
    SELECT  PROMOTION, COUNT(customer_id) as promo_responded, 0 as promo_purchased, 0 as total
    FROM    CUSTOMER
    GROUP BY PROMO_RESPONDED
    UNION   
    SELECT  PROMOTION, COUNT(customer_id) as promo_purchased, 0 as promo_responded, 0 as total
    FROM    CUSTOMER
    GROUP BY PROMO_PURCHASED
)
SELECT  PROMOTION, SUM(promo_responded) as TotalResponded, SUM(promo_purchased) as TotalPurchased, SUM(Total) as TotalSize,
        SUM(promo_responded)/SUM(Total) as ResponseRate, SUM(promo_purchased)/SUM(Total) as PurchaseRate
FROM    tmp

埃庐

这样行吗？我不确定除法和乘法运算符，但我相信我的逻辑很好。关键是在select语句中使用相关的select子语句。

SELECT c.promotion, 
       COUNT(c.customer_id) as promo_size, 
       (SELECT COUNT(customer_id)
          FROM CUSTOMER
         WHERE PROMO_RESPONDED = c.promotion) PROMO_RESPONDED,
       (SELECT COUNT(customer_id)
          FROM CUSTOMER
         WHERE PROMO_PURCHASED = c.promotion) PROMO_PURCHASED,
       (SELECT COUNT(customer_id) *100/count(c.customer_id)
         FROM CUSTOMER
        WHERE PROMO_RESPONDED = c.promotion)                                                      
FROM CUST_PROMO c
GROUP BY c.promotion

使用解码的更干净的解决方案。仍不确定数学是否有效

select PROMOTION, count(CUSTOMER_ID) as promo_size, 
       SUM(DECODE(PROMO_RESPONDED, PROMOTION, 1, 0)) PROMO_RESPONDED,
       SUM(DECODE(PROMO_PURCHASED, PROMOTION, 1, 0)) PROMO PURCHASED,
       SUM(DECODE(PROMO_RESPONDED, PROMOTION, 1, 0))*100/count(CUSTOMER_ID) PROMO_RESPONDED
from CUST_PROMO join CUSTOMER using CUSTOMER_ID
group by PROMOTION

仿普

是的，我认为对这三个汇总查询进行“ 7”处理是可行的。八英镑在那里，以防万一某些促销活动没有反应或没有购买。我也将ѭ9更改为ѭ10。结果是相同的，除非两个表中的customer_id字段可以具有NULL值，这很可能不是这种情况。但是，如果客户可能出现在具有相同促销代码的表的两行中，则应将其更改为COUNT(DISTINCT customer_id)：

SELECT prom.promotion
     , prom.promo_size
     , responded.promo_responded
     , purchased.promo_purchased
     , responded.promo_responded / prom.promo_size
       AS promo_response_success_rate
FROM
    ( SELECT promotion
         , COUNT(*) AS promo_size
      FROM CUST_PROMO
      GROUP BY promotion
    ) AS prom
  LEFT JOIN 
    ( SELECT PROMO_RESPONDED AS promotion
           , COUNT(*) AS promo_responded
      FROM CUSTOMER
      GROUP BY PROMO_RESPONDED
    ) AS responded
    ON responded.promotion = prom.promotion
  LEFT JOIN
    ( SELECT PROMO_PURCHASED AS promotion
           , COUNT(*) AS promo_purchased
      FROM CUSTOMER
      GROUP BY PROMO_PURCHASED
    ) AS purchased
    ON purchased.promotion = prom.promotion

要回复问题请先登录或注册

如何结合SELECT语句以允许我计算SQL Server中的百分比，成功和失败？

4 个回复

发起人

sql

sql_server

tsql

join

问题状态

如何结合SELECT语句以允许我计算SQL Server中的百分比，成功和失败？

与内容相关的链接

4 个回复

发起人

sql

sql_server

tsql

join

问题状态