Есть ли какой-либо способ, которым я могу заполнить объект на основе месяцев, но исключая первый месяц каждого цикла?

#sql #postgresql #google-bigquery

Вопрос:

у меня есть набор образцов данных, подобных этому

 | id | month
| x  | jan
| x  | feb
| x  | mar

есть ли какой-либо способ, которым я могу заполнить этот образец таким образом?

 | id | month | number
| x  | jan   | 1
| x  | feb   | 2
| x  | mar   | 3
| x  | feb   | 1
| x  | mar   | 2
| x  | mar   | 1

это работает как объединение всех, но исключая первый месяц каждого цикла

Ответ №1:

Ниже приведен стандартный SQL BigQuery

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'jan' month, 1 pos UNION ALL
  SELECT 1, 'feb', 2 UNION ALL
  SELECT 1, 'mar', 3 
)
SELECT id, month, 
  pos - MIN(pos) OVER(PARTITION BY id, num)   1 AS number
FROM `project.dataset.table`,
UNNEST(GENERATE_ARRAY(1, pos)) AS num
-- ORDER BY num

с выводом

 Row id  month   number   
1   1   jan     1    
2   1   feb     2    
3   1   mar     3    
4   1   feb     1    
5   1   mar     2    
6   1   mar     1

В случае, если pos поле явно недоступно — вы можете вывести его, как в примере ниже

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'jan' month UNION ALL
  SELECT 1, 'feb' UNION ALL
  SELECT 1, 'mar'
), temp AS (
  SELECT id, month, EXTRACT(MONTH FROM PARSE_DATE('%b', month)) pos
  FROM `project.dataset.table`
)
SELECT id, month, 
  pos - MIN(pos) OVER(PARTITION BY id, num)   1 AS number
FROM temp,
UNNEST(GENERATE_ARRAY(1, pos)) AS num
-- ORDER BY num

с тем же конечным результатом

1. спасибо, чувак, у меня это работает. но, тем не менее, трудно понять логику, стоящую за этим

2. я вернусь позже через день (когда позволит время) с некоторыми объяснениями: o) но пока попробуйте получить его самостоятельно, просто поиграв с ним — чтобы начать добавлять num и pos выбирать список вывода, чтобы вы увидели, как number рассчитывается

Ответ №2:

Если имя таблицы — samples, вы могли бы попробовать этот рекурсивный CTE:

 WITH cte AS
(
    SELECT id
         , month
         , ROW_NUMBER() OVER (ORDER BY id, month) number
    FROM samples
    union ALL
    SELECT id
         , month
         , nr-1
    FROM cte
    where nr>1
)
SELECT 
    id
    ,month
    ,number
FROM cte

Сначала мы нумеруем строки с помощью ROW_NUMBER(), затем мы используем рекурсивный CTE, чтобы сначала вернуть все строки, затем вернуть все строки с номером строки> 1, уменьшая номер строки на 1, это выполняется для того же количества итераций, что и исходное количество строк.

Ответ №3:

 with data as (
    select id, "month",
        case "month"
            when 'jan' then 1 when 'feb' then 2 when 'mar' then 3 when 'apr' then 4
            when 'may' then 5 when 'jun' then 6 when 'jul' then 7 when 'aug' then 8
            when 'sep' then 9 when 'oct' then 10 when 'nov' then 11 when 'dec' then 12
        end as mm
    from T
)
select d1.id, d1."month", row_number() over (partition by d1.id order by d1.mm) as nun
from data d1 cross apply data d2
where d1.mm <= d2.mm