Вам нужно переименовать свой основной класс в Main
, он не может быть чем-то еще.
Неважно, сколько файлов в виде пакетов и классов вы создаете, вы должны назвать свой основной класс Main
.
Вот и все.
Ниже для стандартного SQL BigQuery
В случае, если вас интересует максимальное количество дней подряд пользователей на одном рабочем месте:
#standardSQL
SELECT uid, MAX(consecuitive_days) max_consecuitive_days
FROM (
SELECT uid, grp, COUNT(1) consecuitive_days
FROM (
SELECT uid,
COUNTIF(step > 1) OVER(PARTITION BY uid, worksite_id ORDER BY ts) grp
FROM (
SELECT uid, worksite_id, ts,
DATE_DIFF(ts, LAG(ts) OVER(PARTITION BY uid, worksite_id ORDER BY ts), DAY) step
FROM `project.dataset.table`
)
) GROUP BY uid, grp
) GROUP BY uid
В случае, если рабочее место не имеет значения, и Вы ищете только максимальное количество дней подряд:
#standardSQL
SELECT uid, MAX(consecuitive_days) max_consecuitive_days
FROM (
SELECT uid, grp, COUNT(1) consecuitive_days
FROM (
SELECT uid,
COUNTIF(step > 1) OVER(PARTITION BY uid ORDER BY ts) grp
FROM (
SELECT uid, ts,
DATE_DIFF(ts, LAG(ts) OVER(PARTITION BY uid ORDER BY ts), DAY) step
FROM `project.dataset.table`
)
) GROUP BY uid, grp
) GROUP BY uid
Вы можете протестировать, сыграть любой из вышеперечисленных с выборкой данных из вашего вопроса, как в примере ниже
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'u12345' uid, 'worksite_1' worksite_id, DATE '2019-01-01' ts UNION ALL
SELECT 'u12345', 'worksite_1', '2019-01-02' UNION ALL
SELECT 'u12345', 'worksite_1', '2019-01-03' UNION ALL
SELECT 'u12345', 'worksite_1', '2019-01-04' UNION ALL
SELECT 'u12345', 'worksite_1', '2019-01-06' UNION ALL
SELECT 'u1', 'worksite_1', '2019-01-01' UNION ALL
SELECT 'u1', 'worksite_1', '2019-01-02' UNION ALL
SELECT 'u1', 'worksite_1', '2019-01-05' UNION ALL
SELECT 'u1', 'worksite_1', '2019-01-06'
)
SELECT uid, MAX(consecuitive_days) max_consecuitive_days
FROM (
SELECT uid, grp, COUNT(1) consecuitive_days
FROM (
SELECT uid,
COUNTIF(step > 1) OVER(PARTITION BY uid ORDER BY ts) grp
FROM (
SELECT uid, ts,
DATE_DIFF(ts, LAG(ts) OVER(PARTITION BY uid ORDER BY ts), DAY) step
FROM `project.dataset.table`
)
) GROUP BY uid, grp
) GROUP BY uid
с результатом: [ 118]
Row uid max_consecuitive_days
1 u12345 4
2 u1 2
подходит ли вам это?
set @gr=1;
select uid, max(cnt) max_cnt from (
select uid, grp, count(*) cnt from (
select uid,
case when ifnull(DATE_ADD(oldDate, INTERVAL 1 DAY), timestamp)= timestamp then
@gr
else
@gr := @gr +1
end grp
from
(
SELECT
uid,
timestamp,
lag(timestamp) over (partition by uid order by timestamp asc) as oldDate
FROM OnSite
) t
)t2
group by uid, grp
)t3
group by uid
Результат
| uid | max_cnt |
| ------ | ------- |
| u1 | 2 |
| u12345 | 4 |