先进集团

Question

我有一张5栏的桌子：

property1
property2
property3
rank
date

对于给定的属性元组，我得到一个记录数组，这些记录在rank和date中都是不同的。例如：

p1value_1, p2value_1, p3value_1 100, 2013-01-12
p1value_1, p2value_1, p3value_1 200, 2013-02-12
p1value_1, p2value_1, p3value_1, 75, 2013-03-12

对于下一个可用的属性集，我得到了一些不同的内容。例如：

p1value_1, p2value_1, p3value_2  30, 2013-01-12
p1value_1, p2value_1, p3value_2  15, 2013-02-12
p1value_1, p2value_1, p3value_2, 80, 2013-03-12

(注意从p3value_1到p3value_2的更改)。

我需要弄清楚，在开始日期和结束日期之间的排名变化是正的还是负的。如果级别降低(即级别越低，越好)，则变化被认为是积极的(“向上移动”)。对于上面的一个例子，更改是这样计算的：

-(75-100) =  25 > 0  --  "moved up"
-(80-30)  = -50 < 0  --  "moved down"

任务是为属性的每个可用配置(集)计算所有正更改和所有负更改的数量。

理想情况下，最终返回结果如下所示：

moved up | moved down
---------------------
      13 |         28

我想我可能需要做以下几个步骤：

1. 将我感兴趣的所有记录分成小组(集合)，如上面的例子所示，
2. 按日期对每个小组进行排序，选择第一项和最后一项为first和last，
3. 通过计算决定(向上还是向下)是什么样的变化。 -(最后-第一) -这就是一个小团体的价值
4. 计算“向上移动”组和“向下移动”组的数量并返回答案。

如有任何指导、链接或指导，我们将不胜感激。如果有什么需要澄清的话，请告诉我，我会尽力澄清的。

更新.我在使用postgresql。

Answer 1

语法可能有点不正确，但在postgresql中，您可以使用FIRST_VALUE()和LAST_VALUE()函数，如下所示：

SELECT  property1
       ,property2
       ,property3
       ,last_value(rank) over(partition by property1,property2,property3 order by date)
        - first_value(rank) over(partition by property1,property2,property3 order by date) AS Rank_Change
FROM YourTable

，这将在每一行上返回Rank_Change，然后需要将其分组为每一行1，并使用条件SUM()

SELECT SUM(CASE WHEN Rank_Change < 0 THEN Rank_Change END) AS Total_Rank_Down

更新：

SELECT SUM(CASE WHEN Rank_Change < 0 THEN Rank_Change END) AS Total_Down
      ,SUM(CASE WHEN Rank_Change > 0 THEN Rank_Change END) AS Total_Up
FROM (
      SELECT search_engine,
             domain,
             location,
             MAX(Rank_Change) AS Rank_Change
      FROM (SELECT
             search_engine,
             domain,
             location,
             first_value(rank) over(partition BY search_engine, domain, location ORDER BY date DESC) -
             first_value(rank) over(partition BY search_engine, domain, location ORDER BY date) AS Rank_Change
            FROM ranks
            )AS Sub
      GROUP BY search_engine,
               domain,
               location
     )as SubSub

Last_Value实际上很古怪，因此您需要对两者都使用first_value，并将其中之一的ORDER BY更改为DESC。

演示：SQL Fiddle