Блог

Главная - Вопросы по программированию - Как получить первое и последнее значение для каждого раздела в столбце с помощью SQL

Как получить первое и последнее значение для каждого раздела в столбце с помощью SQL

#sql #pyspark #apache-spark-sql #hiveql #window-functions

#sql #pyspark #apache-spark-sql #hiveql #окно-функции

Вопрос:

У меня есть набор данных, который выглядит как показано ниже.

          ts                c1           c2               c3
2019-01-04T01:50:00.000Z    C   25.48801612854004   33.317527770996094
2019-01-04T01:51:00.000Z    C   25.74610710144043   33.392295837402344
2019-01-04T01:52:00.000Z    C   25.978872299194336  33.29177474975586
2019-01-04T01:53:00.000Z    B   26.12158203125      33.2805061340332
2019-01-04T01:54:00.000Z    B   26.28511619567871   33.26923751831055
2019-01-04T01:55:00.000Z    C   26.470335006713867  33.25796890258789
2019-01-04T01:56:00.000Z    C   26.63957977294922   33.24669647216797
2019-01-04T01:57:00.000Z    C   26.954004287719727  33.23542785644531
2019-01-04T01:58:00.000Z    C   27.08258056640625   33.224159240722656
2019-01-04T01:59:00.000Z    A   27.25551986694336   33.212890625
2019-01-04T02:00:00.000Z    A   27.514263153076172  33.201622009277344
2019-01-04T02:01:00.000Z    A   27.588970184326172  33.17148971557617
2019-01-04T02:02:00.000Z    B   27.727638244628906  33.13819122314453
2019-01-04T02:03:00.000Z    B   27.956039428710938  33.104896545410156
2019-01-04T02:04:00.000Z    B   28.152463912963867  33.10499954223633

Я хочу взять первое и последнее значение «ts» для каждого значения раздела из столбца «c1».
Я попробовал выполнить приведенный ниже запрос, но он не возвращает правильных результатов.

 SELECT ts, c1, c2, c3,
first_value(ts) OVER (partition by c1 order by ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as first,
last_value(ts) OVER (partition by c1 order by ts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as last
FROM `default`.`a07_a15`

Проблема: Первое значение возвращает только три различных значения ts, а максимальное значение возвращает совершенно неверно.

Ожидаемый: мне нужно первое и последнее значение для каждого повторяющегося значения раздела.

          ts                c1           c2               c3                 first                last
2019-01-04T01:50:00.000Z    C   25.48801612854004   33.317527770996094  2019-01-04T01:50:00.000Z    2019-01-04T01:52:00.000Z
2019-01-04T01:51:00.000Z    C   25.74610710144043   33.392295837402344  2019-01-04T01:50:00.000Z    2019-01-04T01:52:00.000Z
2019-01-04T01:52:00.000Z    C   25.978872299194336  33.29177474975586   2019-01-04T01:50:00.000Z    2019-01-04T01:52:00.000Z
2019-01-04T01:53:00.000Z    B   26.12158203125      33.2805061340332    2019-01-04T01:53:00.000Z    2019-01-04T01:54:00.000Z
2019-01-04T01:54:00.000Z    B   26.28511619567871   33.26923751831055   2019-01-04T01:53:00.000Z    2019-01-04T01:54:00.000Z
2019-01-04T01:55:00.000Z    C   26.470335006713867  33.25796890258789   2019-01-04T01:55:00.000Z    2019-01-04T01:58:00.000Z
2019-01-04T01:56:00.000Z    C   26.63957977294922   33.24669647216797   2019-01-04T01:55:00.000Z    2019-01-04T01:58:00.000Z
2019-01-04T01:57:00.000Z    C   26.954004287719727  33.23542785644531   2019-01-04T01:55:00.000Z    2019-01-04T01:58:00.000Z    
2019-01-04T01:58:00.000Z    C   27.08258056640625   33.224159240722656  2019-01-04T01:55:00.000Z    2019-01-04T01:58:00.000Z
2019-01-04T01:59:00.000Z    A   27.25551986694336   33.212890625        2019-01-04T01:59:00.000Z    2019-01-04T02:01:00.000Z
2019-01-04T02:00:00.000Z    A   27.514263153076172  33.201622009277344  2019-01-04T01:59:00.000Z    2019-01-04T02:01:00.000Z
2019-01-04T02:01:00.000Z    A   27.588970184326172  33.17148971557617   2019-01-04T01:59:00.000Z    2019-01-04T02:01:00.000Z
2019-01-04T02:02:00.000Z    B   27.727638244628906  33.13819122314453   2019-01-04T02:02:00.000Z    2019-01-04T02:04:00.000Z
2019-01-04T02:03:00.000Z    B   27.956039428710938  33.104896545410156  2019-01-04T02:02:00.000Z    2019-01-04T02:04:00.000Z
2019-01-04T02:04:00.000Z    B   28.152463912963867  33.10499954223633   2019-01-04T02:02:00.000Z    2019-01-04T02:04:00.000Z

Ответ №1:

Использование lag() и lead() :

 select t.*
from (select t.*,
             lag(c1) over (order by ts) as prev_c1,
             lead(c1) over (order by ts) as next_c1
      from t
     ) t
where prev_c1 is null or next_c1 is null or
      prev_c1 <> c1 or next_c1 <> c1;

Это помещает значения в разные строки. Если вы хотите, чтобы они были в одних и тех же строках, вероятно, рассматривать это как проблему пробелов и островов — самое простое решение:

 select c1, min(ts), max(ts)
from (select t.*,
             row_number() over (order by ts) as seqnum,
             row_number() over (partition by c1 order by ts) as seqnum_2
      from t
     ) t
group by c1, (seqnum - seqnum_2);

Редактировать:

Если вам нужно сохранить исходные строки, просто используйте оконные функции, убедитесь, что псевдонимы совпадают:

 select t.*,
       min(ts) over (partition by c1, (seqnum - seqnum2)) as min_ts,
       max(ts) over (partition by c1, (seqnum - seqnum2)) as max_ts
from (select t.*,
             row_number() over (order by ts) as seqnum,
             row_number() over (partition by c1 order by ts) as seqnum2
      from t
     ) t

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

1. @Linoff вышеупомянутое решение работает нормально (2-е). Но я хочу включить все доступные столбцы в набор данных (ts, c1, c2 и c3). Допустим, у нас есть 4 строки в первом разделе, нам нужно поместить одно и то же постоянное значение (первое и последнее) для 4 строк и так далее

2. @Sudha . , , Первый сохраняет все столбцы как для первой, так и для последней строк.

3. @Linoff извините, я пробовал, но это работает не так, как ожидалось. Нам нужно минимальное и максимальное значение «ts», но оно возвращает значение раздела. Пожалуйста, проверьте ожидаемый результат, который я отредактировал в приведенном выше сообщении.

4. @Sudha . , , Это действительно настройка запроса. Вам не нужна агрегация, просто оконные функции.

5. Отлично!. Теперь он работает так, как ожидалось. Большое спасибо @Linoff.

Метки: