MySQL SELECT 查询将历史记录转换为随时间推移的每周摘要

Question

我有一个历史表(“property_histories”)，用于记录我们属性(property)管理系统中的事件。这些事件可用于确定给定的特性是否可供出租，我正在尝试构建“实时”特性的(每周)摘要。

有问题的 4 个事件是“已发布”、“未发布”、“hidden_​​from_search”和“unhidden_​​from_search”。

要使属性(property)生效，它必须是:

• 已发布。
• 如果它曾经被取消发布，则后续发布的事件必须是最新的。
• 如果它曾被 hidden_​​from_search，则后续的“unhidden_​​from_search”事件一定是最近发生的。

大多数属性都有一个简单的历史记录，很可能由单个“已发布”事件组成，但有些更复杂，示例如下:

property_histories
----------------------------
id   |   property_id |   City     |   status               |   date    
1    |   325407      |   Paris    |   published            |   2014-01-01
2    |   325407      |   Paris    |   hidden_from_search   |   2014-01-24
3    |   325407      |   Paris    |   unhidden_from_search |   2014-02-05
4    |   325407      |   Paris    |   unpublished          |   2014-02-15
5    |   410008      |   London   |   published            |   2014-01-01           
6    |   410008      |   London   |   unpublished          |   2014-01-10
7    |   410008      |   London   |   published            |   2014-01-18

---

我的目标是能够按周计算“实时”属性:

weekly_count
----------------------------
Year  |   Week   |   City     |   Live_Count 
2014  |   1      |   Paris    |   0      
2014  |   1      |   London   |   0
2014  |   2      |   Paris    |   1
2014  |   2      |   London   |   1
2014  |   3      |   Paris    |   1
2014  |   3      |   London   |   0
2014  |   4      |   Paris    |   1
2014  |   4      |   London   |   1
2014  |   5      |   Paris    |   0
2014  |   5      |   London   |   1
2014  |   6      |   Paris    |   0
2014  |   6      |   London   |   1
2014  |   7      |   Paris    |   1
2014  |   7      |   London   |   0
2014  |   8      |   Paris    |   0
2014  |   8      |   London   |   1
2014  |   9      |   Paris    |   0
2014  |   9      |   London   |   1
----------------------------

感谢帮助!!

Answer 1

您自己的测试结果与您的要求不符。您声明直播计数是按周计算的，这意味着伦敦应该在第 1 周直播，因为它在第 1 周发布，然后在第 2 周取消发布。

假设一周从星期日开始(sql 默认值)那么这将起作用。只需输入您自己的日期范围，并将我的数字表替换为您的。

如果您需要星期一作为开始日期，请在查询顶部使用它

SET DATEFIRST 1

模拟你的测试:

-- Create dummy data
CREATE TABLE #property_histories
(
    id int, property_id int, City varchar(50), status varchar(50), date date
)
INSERT INTO #property_histories
    SELECT 1    ,   325407      ,   'Paris'    ,   'published'            ,   '2014-01-01' UNION ALL
    SELECT 2    ,   325407      ,   'Paris'    ,   'hidden_from_search'   ,   '2014-01-24' UNION ALL
    SELECT 3    ,   325407      ,   'Paris'    ,   'unhidden_from_search' ,   '2014-02-05' UNION ALL
    SELECT 4    ,   325407      ,   'Paris'   ,   'unpublished'          ,   '2014-02-15' UNION ALL
    SELECT 5    ,   410008      ,   'London'   ,   'published'            ,   '2014-01-01' UNION ALL        
    SELECT 6    ,   410008      ,   'London'   ,   'unpublished'          ,   '2014-01-10' UNION ALL
    SELECT 7    ,   410008      ,   'London'   ,   'published'            ,   '2014-01-18' 

现在是代码:

    -- TODO: Set your date range
    DECLARE @SD Datetime = '2014-01-01'
    DECLARE @ED Datetime = '2014-12-31'
    DECLARE @Wks INT = Datediff(week,@SD,@ED) -- Don't change this

    -- Generate dates table
    SELECT  NumberID as 'Week', 
            DATEADD(DAY, 1-DATEPART(WEEKDAY, DateAdd(week,NumberID-1,@SD)), DateAdd(week,NumberID-1,@SD)) as 'WeekStart', 
            DATEADD(DAY, 7-DATEPART(WEEKDAY, DateAdd(week,NumberID-1,@SD)), DateAdd(week,NumberID-1,@SD)) as 'WeekEnd'
    INTO    #Dates
    FROM    Generic.tblNumbers  -- TODO: use your own Numbers table here
    WHERE   NumberID BETWEEN 1 AND @Wks

-- Now generate report 
SELECT  T.Year, T.Week, T.City, 
        SUM(CASE    WHEN PH1.status = 'published' THEN 1
                    WHEN PH1.status = 'unhidden_from_search' THEN 1
                    ELSE 0 END) as 'Live_Count'

FROM #Dates D1
LEFT JOIN
    -- Get latest date per week
    (SELECT YEAR(D.WeekStart) as 'Year',
            D.Week,
            PH.City,
            PH.property_ID,
            MAX(PH.date) as MaxDate

        FROM    #Dates D
        LEFT JOIN   #property_histories PH
                ON  PH.date BETWEEN @SD AND D.WeekEnd
        GROUP BY D.WeekStart, D.Week, D.WeekStart, D.WeekEnd, PH.City, PH.property_id
    ) T
    ON T.Week = D1.Week

LEFT JOIN #property_histories PH1
        ON PH1.City = T.City AND PH1.property_id = T.property_id AND PH1.date = T.MaxDate

GROUP BY T.Year, T.Week, T.City

分解逻辑:首先，我创建了一个包含周数、周开始和周结束日期的帮助表。周开始基本上是多余的，但可能对报告有用。

然后我进行子查询以获取与每周/城市/属性(property)相关的最新日期。对于这个“最大”日期、城市和属性(property)，我得到状态，如果它是实时的，我将它加起来。所以通俗地说;每周获取每个城市每个属性的最新状态和 SUM(如果实时)。

与发布的其他答案不同，此解决方案可弥补数据中的空白。如果一个城市和特性的最新状态记录实际上一直追溯到第 1 周，那么它在随后的任何一周仍然有效。