使用案例报表获取每周总订单，已取消订单和已取消订单的百分比 [英] Using case statement to get weekly count of total orders, cancelled orders and % of cancelled orders

问题描述

初次张贴，只是在寻找撰写案例陈述的指导.我想从一个表中提取以下内容

first time poster and just looking for some guidance on writing a case statement. I'd like to pull the following from a single table

每周总订单 每周取消的订单 已取消订单的百分比

total orders by week cancelled orders by week % of orders cancelled

我对案例陈述进行了一些阅读，但找不到确切的内容.我认为case语句类似于

I did some reading on case statements, but couldn't find exactly what I was looking for. I assume the case statement would be something along the lines of

当order ='canceled'然后计数订单的情况下"，但是我也知道这是错误的，因此只需要寻求帮助即可.

"case when order = 'cancelled' THEN count orders", but I also know that's wrong, so just looking for some assistance.

提前谢谢！

-ET

推荐答案

您可能应该为此构建一个嵌套查询: 首先，建立查询以按周对事物进行排序.

You should probably build a nested query for this: First, build you query to get things sorted by week.

SELECT orderID, orderStatus, DATEPART(week, orderDate) as orderWeek
FROM orders

那么您可能可以从中进行选择，如下所示:

Then you can probably get a select from that as follows:

SELECT count(orderID) OVER(PARTITION BY orderWeek) AS Total
,count(orderID) OVER(PARTITION BY orderWeek, orderStatus) AS CountPerStatus
,orderWeek
FROM (SELECT orderID, orderStatus, DATEPART(week, orderDate) as orderWeek
FROM orders) a

添加，然后将总计总计为一行:

add then finally get your totals on one row:

SELECT Total, CountPerStatus, orderWeek
FROM (
    SELECT count(orderID) OVER(PARTITION BY orderWeek) AS Total
    ,count(orderID) OVER(PARTITION BY orderWeek, orderStatus) AS CountPerStatus
    ,orderWeek
    FROM (
        SELECT orderID, orderStatus, DATEPART(week, orderDate) as orderWeek
        FROM orders
) a
) b
WHERE CountPerStatus = 'Cancelled'

这显然可以简化，但是我更愿意将其显式化以便更好地理解.希望对您有所帮助.

This can clearly be simplified but I prefer to explicit it for better understanding. Hope it helps.

这篇关于使用案例报表获取每周总订单，已取消订单和已取消订单的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！