sqlite：获得每个项目的前X％的平均值

问题描述：

是否有可能获得组中前X％项的平均值？sqlite：获得每个项目的前X％的平均值

例如：
我有一个表，它有一个item_id，时间戳和价格列。输出应按item_id和timestamp分组，并且'price-column'应该取平均值。对于平均值，只能使用该组内最低的X％价格。

我发现了类似的问题（How to select top x records for every group），但这不适用于sqlite。

“最低X％价格”是什么意思？百分比的基础是什么？价钱？计数？你能举一个例子吗？ – 2013-04-08 12:06:16

按时间戳分组是否有意义？你可以用更清晰的词语来表达你想要达成的目标 - 查询应该返回什么？ – Tim 2013-04-08 12:51:45

@Tim是的，这对我而言很有意义，因为有许多项目具有绝对相同的时间戳（因为它们是作为批次插入/更新的） – Daniel 2013-04-08 13:46:41

答

获得每个组中最高的n条记录需要计数。假设有没有重复，下面的查询返回的记录数的一个项目：

select t.*, 
     (select count(*) from t t2 where t2.item_id = t.item_id 
     ) as NumPrices 
from t

这就是所谓的相关子查询。现在，让我们延长了主意，包括一个等级，然后算出平均值右侧组：

select item_id, avg(price) 
from (select t.*, 
      (select count(*) from t t2 where t2.item_id = t.item_id 
      ) as NumPrices, 
      (select count(*) from t t2 where t2.item_id = t.item_id and t2.price <= t.price 
      ) as PriceRank 
     from t 
    ) t 
where (100.0*PriceRank/NumPrices) <= X 
group by item_id

为了提高性能，你会希望在(item_id, price)的索引。

我试过了 - 而查询本身是有道理的 - 它超级慢（10分钟后没有完成）。我的表有〜1000000个记录，应该被分组到〜8000条记录。无论如何，我会接受答案 – Daniel 2013-04-08 13:49:54

你有什么指数？ – 2013-04-08 14:46:08

目前仅在价格栏 – Daniel 2013-04-08 21:26:06

答

要使用ID I和时间戳T得到的记录数的组中，使用此查询：

SELECT COUNT(*) 
FROM MyTable 
WHERE item_id = I 
    AND timestamp = T

要得到限制，与X繁殖，并使用ROUND/CAST转换为整数：

SELECT CAST(ROUND(COUNT(*) * X/100) AS INTEGER) 
FROM MyTable 
WHERE item_id = I 
    AND timestamp = T

要获得一个特定的组是限制内的所有记录，命令组由价格中的记录，并限制返回的计数：

SELECT * 
FROM MyTable 
WHERE item_id = I 
    AND timestamp = T 
ORDER BY price 
LIMIT (SELECT CAST(ROUND(COUNT(*) * X/100) AS INTEGER) 
     FROM MyTable 
     WHERE item_id = I 
     AND timestamp = T)

从理论上讲，要获得组平均值，加上GROUP BY围绕：

SELECT item_id, 
     timestamp, 
     (SELECT AVG(price) 
     FROM (SELECT price 
       FROM MyTable T2 
       WHERE T2.item_id = T1.item_id 
       AND T2.timestamp = T1.timestamp 
       ORDER BY price 
       LIMIT (SELECT CAST(ROUND(COUNT(*) * X/100) AS INTEGER) 
        FROM MyTable T3 
        WHERE T3.item_id = T1.item_id 
         AND T3.timestamp = T1.timestamp) 
      ) 
     ) AS AvgPriceLowestX 
FROM MyTable T1 
GROUP BY item_id, 
     timestamp

然而，似乎SQLite不容许从LIMIT子句中访问相关的变量，因此这并在实践中不工作。您必须获取所有组的ID（SELECT DISTINCT item_id, timestamp FROM MyTable）并针对每个组执行上面的第三个查询。

在任何情况下，请确保在三列item_id,timestamp和price上有一个索引以获得良好性能。

sqlite：获得每个项目的前X％的平均值

相关推荐