仅显示最新可用事件类型的值 [英] Only display values of latest available event type

问题描述

SQL小提琴:

SQL Fiddle:

CREATE TABLE Purchasing (
    Event_Type VARCHAR(255),
    Campaign VARCHAR(255),
    Quantity_Offer VARCHAR(255),
    Quantity_Order VARCHAR(255),
    Quantity_Received VARCHAR(255)
);

INSERT INTO Purchasing
(Event_Type, Campaign, Quantity_Offer, Quantity_Order, Quantity_Received)
VALUES 
("Offer", "C001", "300", NULL, NULL),
("Offer", "C002", "200", NULL, NULL),
("Offer", "C003", "500", NULL, NULL),
("Offer", "C004", "600", NULL, NULL),
("Offer", "C005", "400", NULL, NULL),
("Offer", "C006", "300", NULL, NULL),
("Order", "C001", NULL, "320", NULL),
("Order", "C002", NULL, "180", NULL),
("Order", "C003", NULL, "450", NULL),
("Order", "C004", NULL, "630", NULL),
("Received", "C001", NULL, NULL, "310"),
("Received", "C002", NULL, NULL, "190");

在上表中，使用Event_Type和适用于Event_Type的Quantity显示了不同广告系列的购买过程.每个广告系列只能有一个Event_Type一次.

In the table above the purchasing process for different campaigns is displayed using an Event_Type and a Quantity that applies to the Event_Type. Every campaign can only have each Event_Type once.

现在，我希望在购买过程中为每个Campaign获取最新可用状态的Quantity.
总结逻辑如下:

Now, I want ot get the Quantity of the latest available status within the purchasing process for each Campaign.
Summarized the logic is the following:

Received kills Order
Order kills Offer

因此，结果应如下所示:

Therefore, the result should look like this:

Campaign        Event_Type        Quantity
C001              Received          310
C002              Received          190
C003              Order             450
C004              Order             630
C005              Offer             400
C006              Offer             300

为了实现这一点，我尝试使用CASE函数，但无法使其起作用:

In order to achieve this I tried to go with the CASE function but could not make it work:

SELECT
Campaign,
Event_Type,
(CASE 
 WHEN Event_Type = "Order"  THEN SUM(Quantity_Order) 
 WHEN Event_Type = "Offer"  THEN SUM(Quantity_Offer) 
 ELSE SUM(Quantity_Received) 
 END) AS Quantity
FROM Purchasing
GROUP BY 1;

要使其正常运行，我需要更改什么?

What do I need to change to make it work?

推荐答案

这是优先级查询.

在MySQL 8+中，使用窗口函数:

In MySQL 8+, use window functions:

select campaign, event_type,
       coalesce(Quantity_Offer, Quantity_Order, Quantity_Received) as quantity
from (select p.*,
             row_number() over (partition by campaign order by field(event_type, 'Received', 'Order', 'Offer')) as seqnum
      from Purchasing p
     ) p
where seqnum = 1;

在早期版本中，一个简单的方法是相关子查询:

In earlier versions, a simple method is a correlated subquery:

select campaign, event_type,
       coalesce(Quantity_Offer, Quantity_Order, Quantity_Received) as quantity
from Purchasing p
where event_type = (select p2.event_type
                    from Purchasing p2
                    where p2.campaign = p.campaign
                    order by field(p2.event_type, 'Received', 'Order', 'Offer')
                    limit 1
                   );

如果您有大量数据，则需要此查询的Purcahsing(campaign, event_type)上的索引.

If you have a lot of data, you want an index on Purcahsing(campaign, event_type) for this query.

这里是db 小提琴.

Here is a db<>fiddle.

这篇关于仅显示最新可用事件类型的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！