获取停车数据的历史计数和当前计数
我以前在这里得到了很好的帮助，所以在分析停车数据方面。这是我的疑问：
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回答 1
Stack Overflow用户
回答已采纳
发布于 2015-02-04 07:56:02
使用使用为daily count和avg count minus today创建临时表，并连接这些表以获得所需的结果
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28172966
复制相关文章
相似问题
腾讯云开发者
