主页 / dba / 问题 / 39148
Accepted
DaveB
Asked: 2013-04-04 06:31:51 +0800 CST

Postgresql 查询趋势

  • 772

首先很抱歉，如果这是重复的，我对 SQL 还很陌生，所以我不确定在我的搜索中使用什么正确的术语

所以我有一个记录赛车的数据库，具有以下简化模式

race_table
==========
race_id PK
race_date timestamp
average_speed Decimal
max_speed Decimal

drivers
=======
driver_id PK
driver_name text
date_started timestamp

driver_races
============
driver_id FK
race_id FK

如果每个车手在 2/3 年内有大约 1000 场比赛

例如，我将如何查询给定日期范围内平均比赛速度的总体百分比变化（正负）

前 6 个月的百分比变化

Joe Smith - 5% increase
Andy James - 4% increase

前 12 个月的百分比变化

Joe Smith - 8% increase
Lewis May - 6% increase

更新：有关百分比变化的更多详细信息

百分比变化可以使用线性回归计算，（最小二乘拟合将是合适的），平均变化实际上是最佳拟合线上的 y 差，其中每个点都是一场比赛，x 是race_date 和 y 是那场比赛的平均速度。

Postgres 的 regr_slope 将给出与 %change 相同的线的梯度

SELECT regr_slope(r.average_speed, EXTRACT(EPOCH FROM r.race_date)) as slope
    FROM race_table as r, driver_races as dr
    WHERE dr.race_id = r.race_id
    AND d.driver_id = 1

这给出了我想要的数字，但我现在需要针对所有用户应用，按“斜率”排序

postgresql postgresql-9.0

1 个回答

  1. Best Answer

    数据模型

    您正在节省平均值。和最大。每场比赛的速度，而不是每个车手？通常这会是这样的：

    创建表比赛（
  race_id 序列主键
 ,race_date 时间戳
);

创建表驱动程序（
  driver_id 串行主键
 ,driver_name 文本
 ,date_started 时间戳
);

创建表 driver_race (
  driver_id int 参考驱动程序（driver_id）
 ,race_id int 参考种族（race_id）
,average_speed 数值
 ,max_speed 数值
);

    这也是您应该提供数据模型的方式：作为CREATE准备用于测试的脚本。

    numeric并且decimal在 Postgres 中是相同的。

    询问

    您的查询扩展到所有驱动程序：

    SELECT driver_id, d.driver_name, x.slope
FROM   driver d
JOIN  (
   SELECT dr.driver_id
         ,regr_slope(dr.average_speed, EXTRACT(EPOCH FROM r.race_date)) AS slope
   FROM   race r
   JOIN   driver_race dr USING (race_id)
   WHERE  r.race_date BETWEEN '2013-3-1'::date AND '2013-3-31'::date 
   GROUP  BY dr.driver_id
   ) x USING (driver_id)
ORDER  BY slope DESC;

    简单的替代方案

    一个简单的替代方法是将时间段前半段的平均速度与后半段的平均速度相匹配：

    SELECT driver_id, d.driver_name
      ,round(x.avg1::numeric, 2) AS avg1
      ,round(x.avg2::numeric, 2) AS avg2
      ,round((x.avg2 / x.avg1 - 1) * 100, 2) AS pct_change
FROM   driver d
JOIN  (
   SELECT dr.driver_id
         ,avg(CASE WHEN r.race_date BETWEEN '2013-3-01'::date AND '2013-3-15'::date
              THEN dr.average_speed END) AS avg1
         ,avg(CASE WHEN r.race_date BETWEEN '2013-3-16'::date AND '2013-3-31'::date
              THEN dr.average_speed END) AS avg2
   FROM    race r
   JOIN   driver_race dr USING (race_id)
   WHERE  r.race_date BETWEEN '2013-3-1'::date AND '2013-3-31'::date 
   GROUP  BY dr.driver_id
   ) x USING (driver_id)
ORDER  BY pct_change DESC;

    该CASE表达式没有ELSE分支，因为默认值NULL很好：avg()忽略NULL值。

    -> SQLfiddle演示。

    • 4

相关问题