问获取停车数据的历史计数和当前计数
EN

Stack Overflow用户

提问于 2015-01-27 06:34:35

回答 1查看 125关注 0票数 0

我以前在这里得到了很好的帮助，所以在分析停车数据方面。这是我的疑问：

select parking_meter_id, avg(cnt) from
  (select parking_meter_id, count(*) as cnt, to_char(start,'YYYYMMDD') as day
  from parking_transactions
  where start >= now() - interval '3 month' -- last three months
  and to_char(start,'YYYYMMDD') < to_char(now(),'YYYYMMDD') -- but not today
  and to_char(start,'D') = to_char(now(),'D') -- same weekday
  and to_char(now(),'HH24MISS') between to_char(start,'HH24MISS') and to_char(stop,'HH24MISS') -- same time
  group by parking_meter_id, to_char(start,'YYYYMMDD') -- group by day
) as parking_transactions group by parking_meter_id

它确实工作并且显示了对活动事务的平均计数，这是因为从今天开始的事务(now())被过滤掉了。

在相同的运行过程中，查询是否还可以返回当前的活动事务：

select count(*) as cnt from parking_transactions where now() between start and stop

那么，我们可以很容易地比较当前的状况和历史吗？

我的桌子结构是：

parking_meter_id, start, stop

目前，我得到了以下输出：

parking_meter_id, avg(cnt) minus today

我希望有以下产出：

parking_meter_id, avg(cnt) minus today, count(*) for today only

忽略今天事务的where子句是--但不是今天。

到目前为止，输出的一个例子如下：

 parking_meter_id | cnt |   day    
------------------+-----+----------
             4406 |   1 | 20141217
             4406 |   5 | 20150107
             4406 |   1 | 20150121
             4406 |   3 | 20150128
             4406 |   3 | 20150114

我想返回：

 parking_meter_id | avg(cnt-without-today) |   cnt-day    
------------------+-----+------------------------------
             4406 |   2.6                  | 3

postgresql

sql

人脸核身产品特惠

用户政务民生、金融、通信运营商等场景，接入全面，安全可靠新用户3折起

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-02-04 07:56:02

使用使用为daily count和avg count minus today创建临时表，并连接这些表以获得所需的结果

SQL Fiddle

SQL

WITH daily_count AS  -- temp table to store daily counts 
(
         SELECT   parking_meter_id,
                  COUNT(*)                  AS cnt,
                  to_char(start,'YYYYMMDD') AS day
         FROM     parking_transactions
         WHERE    start >= now() - interval '3 month' -- last three months
         AND      to_char(start,'D') = to_char(now(),'D') -- same weekday
         AND      to_char(now(),'HH24MISS') BETWEEN to_char(start,'HH24MISS') AND to_char(stop,'HH24MISS') -- same time
         GROUP BY parking_meter_id,
                  to_char(start,'YYYYMMDD') -- group by parking meter id and day
), avg_count_minus_today AS -- temp table to store avg count minus today 
(
         SELECT   parking_meter_id,
                  AVG(cnt) AS avg_count
         FROM     daily_count
         WHERE    day < to_char(now(),'YYYYMMDD') -- but not today
         GROUP BY parking_meter_id 
)
SELECT     a.parking_meter_id,
           a.avg_count, --avg count minus today
           d.cnt AS today_count
FROM       avg_count_minus_today a
INNER JOIN daily_count d
ON         a.parking_meter_id= d.parking_meter_id AND d.day=to_char(now(),'YYYYMMDD'); --today in daily count