Блог

Главная - Вопросы по программированию - Сдвиньте числовое значение так, чтобы оно было больше левого столбца

Сдвиньте числовое значение так, чтобы оно было больше левого столбца

#sql #google-bigquery

#sql #google-bigquery

Вопрос:

у меня есть таблица результатов, подобная этой

 id | p1 | p2 | p3 | p4
a  | 1  |  1 | 2  | 5
b  | 2  |  3 | 5  | 5
c  | 2  |  2 | 2  | 2

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

Ожидаемый результат

 id | p1 | p2 | p3 | p4
a  | 1  | 2  | 3  | 5
b  | 2  | 3  | 5  | 6
c  | 2  | 3  | 4  | 5

Логика, стоящая за этим (a):

  1. p1 = p2 -> сдвиньте значение p2 до 2 [p1 = 1, p2 = 2]
  2. создать p2 = p3 -> сдвинуть значение p3 до 3 [p1 = 1, p2 = 2, p3 = 3]
  3. завершение, потому что все значения не совпадают в строке

Комментарии:

1. как вычисляется p4?

2. @Fahmi это то же самое, но в случае, который я привожу (id: a), это остановка до настройки p3, потому что все значения больше не совпадают. p1 = 1, p2 = 2, p3 = 3 и p4 = 5.

Ответ №1:

Я интерпретирую эту проблему как увеличение значений p столбцов до тех пор, пока все они не увеличат значение distinct в строке.

Я думаю, что это по своей сути итеративная проблема. Это предполагает подход грубой силы — вычисление каждого нового p значения отдельно.

На самом деле, это не так уж плохо:

 with t as (
      select 'a' as id, 1 as p1, 1 as p2, 2 as p3, 5 as p4 union all
      select 'b', 2, 3, 5, 5 union all
      select 'd', 2, 2, 3, 3 union all
      select 'c', 2, 2, 2, 2
     )
select t.*,
       (case when p4 <= new_p3 then new_p3   1 else p4 end) as new_p4
from (select t.*,
             (case when p3 <= new_p2 then new_p2   1 else p3 end) as new_p3
      from (select t.*, p1 as new_p1,
                   (case when p2 <= p1 then p1   1 else p2 end) as new_p2,
            from t
           ) t
     ) t;

Обратите внимание, что я добавил новую строку для тестирования, 2/2/3/3. Это также кажется сложным случаем.

Ответ №2:

Ниже приведен стандартный SQL для BigQuery и используется JS UDF

 #standardSQL
CREATE TEMP FUNCTION slider(t STRUCT<p1 INT64, p2 INT64, p3 INT64, p4 INT64>)
RETURNS STRUCT<p1 INT64, p2 INT64, p3 INT64, p4 INT64>
LANGUAGE js AS """
  r = []; for (v in t) r.push(t[v]);
  prev = r[0]; for (i = 1; i < r.length; i  ) {
    if(r[i] <= prev) r[i] =   prev;
    prev = r[i];
  }
  i = 0; for (v in t) t[v] = r[i  ];  
  return t;
""";
SELECT id, slider(STRUCT(p1, p2, p3, p4)).*
FROM `project.dataset.table`

Вы можете протестировать, поиграть с приведенным выше примером данных из вашего вопроса, как в примере ниже

 #standardSQL
CREATE TEMP FUNCTION slider(t STRUCT<p1 INT64, p2 INT64, p3 INT64, p4 INT64>)
RETURNS STRUCT<p1 INT64, p2 INT64, p3 INT64, p4 INT64>
LANGUAGE js AS """
  r = []; for (v in t) r.push(t[v]);
  prev = r[0]; for (i = 1; i < r.length; i  ) {
    if(r[i] <= prev) r[i] =   prev;
    prev = r[i];
  }
  i = 0; for (v in t) t[v] = r[i  ];  
  return t;
""";
WITH `project.dataset.table` AS (
  SELECT 'a' AS id, 1 AS p1, 1 AS p2, 2 AS p3, 5 AS p4 UNION ALL
  SELECT 'b', 2, 3, 5, 5 UNION ALL
  SELECT 'c', 2, 2, 2, 2 UNION ALL
  SELECT 'd', 2, 2, 3, 3 
)
SELECT id, slider(STRUCT(p1, p2, p3, p4)).*
FROM `project.dataset.table`

с выводом

 Row id  p1  p2  p3  p4   
1   a   1   2   3   5    
2   b   2   3   5   6    
3   c   2   3   4   5    
4   d   2   3   4   5