计算前一天和今天 [英] Count the day before and the present day
本文介绍了计算前一天和今天的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个代码，可以按出发日期对从公共汽车出发的每条路线进行计数，但是我需要对从148号路线出发的前一天加上139号路线的任何一天进行计数.
I have a code to count every route from bus by departure date, but i need to count the day before from route 148 plus the count of the route 139 for any day.
我有一个带有变量的视图:DEP_DATE(DATE)，DepartureDate(Datetime)，路线(数字)是这样的
I have a view with variables: DEP_DATE (DATE), DepartureDate (Datetime), Routes (Numeric) like this
DEP_DATE DepartureDate Route= 139 Route= 148 Route=129 Route=61 Route=134 Route=60
08/02/2019 2019-02-08T15:00:00 0 0 0 0 0 0
08/02/2019 2019-02-08T10:45:00 0 0 0 0 0 0
08/02/2019 2019-02-08T08:30:00 0 0 0 0 0 0
08/02/2019 2019-02-08T08:15:00 0 0 0 0 0 0
08/02/2019 2019-02-08T21:00:00 0 0 0 0 0 0
08/02/2019 2019-02-08T13:00:00 0 0 0 0 0 0
08/02/2019 2019-02-08T06:30:00 0 0 0 11 0 0
08/02/2019 2019-02-08T19:00:00 0 0 21 0 0 0
08/02/2019 2019-02-08T06:00:00 0 0 0 0 10 13
08/02/2019 2019-02-08T17:30:00 0 0 2 0 0 0
08/02/2019 2019-02-08T05:30:00 1 0 0 0 0 0
08/02/2019 2019-02-08T14:45:00 0 0 0 0 0 0
08/02/2019 2019-02-08T07:00:00 0 0 0 0 0 0
09/02/2019 2019-02-09T20:15:00 0 0 0 0 0 0
09/02/2019 2019-02-09T22:00:00 0 2 0 0 0 0
09/02/2019 2019-02-09T20:30:00 0 0 8 0 0 0
09/02/2019 2019-02-09T08:30:00 0 0 0 0 0 0
09/02/2019 2019-02-09T07:00:00 0 0 0 12 0 0
09/02/2019 2019-02-09T19:00:00 0 0 12 0 0 0
09/02/2019 2019-02-09T06:00:00 0 0 0 0 20 7
09/02/2019 2019-02-09T15:00:00 0 0 0 0 0 0
09/02/2019 2019-02-09T06:30:00 0 0 0 0 0 0
09/02/2019 2019-02-09T08:15:00 0 0 0 0 0 0
09/02/2019 2019-02-09T18:15:00 0 0 0 0 0 0
09/02/2019 2019-02-09T14:45:00 0 0 0 0 0 0
09/02/2019 2019-02-09T13:00:00 0 0 0 0 0 0
10/02/2019 2019-02-10T21:00:00 0 0 0 0 0 0
10/02/2019 2019-02-10T10:45:00 0 0 0 0 0 0
10/02/2019 2019-02-10T06:00:00 0 0 0 0 11 11
10/02/2019 2019-02-10T13:00:00 0 0 0 0 0 0
10/02/2019 2019-02-10T08:30:00 0 0 0 0 0 0
10/02/2019 2019-02-10T08:15:00 0 0 0 22 0 0
10/02/2019 2019-02-10T19:00:00 0 0 21 0 0 0
10/02/2019 2019-02-10T07:00:00 0 0 0 0 0 0
10/02/2019 2019-02-10T20:15:00 0 0 0 0 0 0
10/02/2019 2019-02-10T15:00:00 0 0 0 0 0 0
10/02/2019 2019-02-10T20:30:00 0 1 2 0 0 0
10/02/2019 2019-02-10T06:30:00 0 0 0 0 0 0
10/02/2019 2019-02-10T18:15:00 0 0 0 10 0 0
11/02/2019 2019-02-11T19:00:00 0 0 32 0 0 0
11/02/2019 2019-02-11T08:30:00 0 0 0 0 0 0
11/02/2019 2019-02-11T06:00:00 0 0 0 0 14 12
11/02/2019 2019-02-11T00:45:00 0 0 0 0 0 0
11/02/2019 2019-02-11T15:00:00 0 0 0 0 0 0
11/02/2019 2019-02-11T08:15:00 0 0 0 0 0 0
11/02/2019 2019-02-11T13:00:00 0 0 0 0 0 0
11/02/2019 2019-02-11T06:30:00 0 0 0 0 0 0
11/02/2019 2019-02-11T07:00:00 0 0 0 0 0 0
11/02/2019 2019-02-11T10:45:00 0 0 0 0 0 0
12/02/2019 2019-02-12T08:30:00 0 0 0 0 0 0
12/02/2019 2019-02-12T13:00:00 0 0 0 0 0 0
12/02/2019 2019-02-12T06:00:00 0 0 0 0 10 8
12/02/2019 2019-02-12T15:00:00 0 0 0 0 0 0
12/02/2019 2019-02-12T10:45:00 0 0 0 0 0 0
12/02/2019 2019-02-12T07:00:00 0 0 0 0 0 0
12/02/2019 2019-02-12T14:45:00 0 0 0 15 0 0
12/02/2019 2019-02-12T19:00:00 0 0 14 0 0 0
12/02/2019 2019-02-12T22:00:00 0 2 0 0 0 0
13/02/2019 2019-02-13T13:00:00 0 0 0 0 0 0
13/02/2019 2019-02-13T18:15:00 0 0 0 0 0 0
13/02/2019 2019-02-13T08:15:00 0 0 0 0 0 0
13/02/2019 2019-02-13T20:15:00 0 1 0 0 0 0
13/02/2019 2019-02-13T15:00:00 0 0 0 0 0 0
13/02/2019 2019-02-13T14:45:00 0 0 0 0 0 0
13/02/2019 2019-02-13T08:30:00 0 0 0 0 0 0
13/02/2019 2019-02-13T07:00:00 0 0 0 0 0 0
13/02/2019 2019-02-13T06:00:00 0 0 0 0 7 7
13/02/2019 2019-02-13T21:00:00 0 0 0 0 0 0
13/02/2019 2019-02-13T06:30:00 0 0 0 3 0 0
13/02/2019 2019-02-13T19:00:00 0 0 24 0 0 0
14/02/2019 2019-02-14T18:15:00 0 0 0 0 0 0
14/02/2019 2019-02-14T20:30:00 0 0 3 0 0 0
14/02/2019 2019-02-14T07:00:00 0 0 0 0 0 0
14/02/2019 2019-02-14T06:00:00 0 0 0 0 4 2
14/02/2019 2019-02-14T15:00:00 0 0 0 10 0 0
14/02/2019 2019-02-14T19:00:00 0 0 10 0 0 0
14/02/2019 2019-02-14T13:00:00 2 0 0 0 0 0
14/02/2019 2019-02-14T08:30:00 0 0 0 0 0 0
让我编写的代码如下:
SELECT
DEP_DATE,
COUNTIF( RouteId = 139) + COUNTIF( RouteId = 148 AND DepartureDate =
DATETIME_SUB(departureDate, INTERVAL 1 DAY)) AS BUS_1,
COUNTIF( RouteId = 134 ) + COUNTIF( RouteId = 60 ) AS BUS_2,
COUNTIF( RouteId = 134 AND EXTRACT(HOUR FROM DepartureDate) = 6) +
COUNTIF( RouteId = 60 AND EXTRACT(HOUR FROM DepartureDate) = 6) AS
BUS_3,
FROM
`project.dataset.view`
WHERE
DepartureDate >
DATETIME_TRUNC(DATETIME_SUB(CURRENT_DATETIME("America/Lima"), INTERVAL 3
DAY),DAY)
GROUP BY
DEP_DATE
我的结果是这样的
DEP_DATE Bus_1 Bus_2 Bus_3 Explanation_Bus_1: Route_139 Route_148
08/02/2019 1 34 23 1 0
09/02/2019 2 32 27 0 2
10/02/2019 1 45 22 0 1
11/02/2019 0 42 26 0 0
12/02/2019 2 29 18 2 0
13/02/2019 0 27 14 0 1
14/02/2019 3 23 6 2 0
但是我期望我对"Bus_1"的计数是这样的:
But what i expect my count on "Bus_1" like this:
DEP_DATE Bus_1 Bus_2 Bus_3 Explanation_Bus_1: Route_139 Route_148
08/02/2019 1 34 23 1 0
09/02/2019 0 32 27 0 2
10/02/2019 2 45 22 0 1
11/02/2019 1 42 26 0 0
12/02/2019 2 29 18 2 0
13/02/2019 0 27 14 0 1
14/02/2019 3 23 6 2 0
每条路线148的计数必须在公交1的第二天进行计数
Every count of the route 148 has to be count the day after in the Bus 1
推荐答案
您需要检查日期是星期五，星期三还是星期六.如果是那一天中的某一天，则计算否则默认为0.
You would need to check if the date is Friday, Wednesday or Saturday. If it is one of those days, then calculate else default to 0.
大查询条件表达式: https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions
大查询日期函数: https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions
- DAYOFWEEK:返回范围为[1,7]的值，其中星期日为一周的第一天.
尝试一下:
SELECT
T1.DEP_DATE,
T2.BUS_1 AS BUS_1,
COUNTIF( RouteId = 134 ) + COUNTIF( RouteId = 60 ) AS BUS_2,
COUNTIF( RouteId = 134 AND EXTRACT(HOUR FROM DepartureDate) = 6) +
COUNTIF( RouteId = 60 AND EXTRACT(HOUR FROM DepartureDate) = 6) AS
BUS_3,
FROM
`project.dataset.view` T1
LEFT JOIN (
SELECT
DATEADD(DEP_DATE, 1, DAY) AS DEP_DATE,
COUNTIF(RouteId = 139) + COUNTIF(RouteId = 148) AS BUS_1
FROM project.dataset.view
GROUP BY DEP_DATE
) T2
ON T1.DEP_DATE = T2.DEP_DATE
WHERE
DepartureDate >
DATETIME_TRUNC(DATETIME_SUB(CURRENT_DATETIME("America/Lima"), INTERVAL 3
DAY),DAY)
GROUP BY
T1.DEP_DATE, T2.BUS_1
这篇关于计算前一天和今天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
