With select: PostgreSQL : Документация: 9.5: 7.8. Запросы WITH (Общие табличные выражения) : Компания Postgres Professional

Как использовать конструкцию SELECT FROM UNNEST для анализа параметров в повторяющихся записях Google BigQuery / Хабр

В предыдущей статье я показал вам, как использовать функцию UNNEST в BigQuery для анализа параметров событий в данных Google Analytics для Firebase.

Мы использовали функцию UNNEST, потому что обычно параметры события хранятся как повторяющаяся запись (repeated record), которую вы можете рассматривать как массив, напоминающий JSON структуру. Вы можете разбить этот массив, и поместить каждый отдельный его элемент (параметр события) в новую строку, а затем скопировать исходную строку для каждого из этих отдельных параметров. Более понятно будет если посмотреть следующую анимацию:

Рассмотренный ранее пример подходит в тех случаях, когда вам необходимо проанализировать один из параметров событий. Но что делать, если нам необходимо проанализировать сразу несколько параметров события?

Например, давайте взглянем на наш образец игры Flood-it. Вот ссылка на общедоступный набор данных по этой игре, если вы хотите повторить описанные ниже манипуляции.

Если вы посмотрите на событие level_complete_quickplay, то найдёте два интересующие нас параметра: параметр value, который сообщает нам окончательный счет игры (то есть сколько ходов потребовалось пользователю для прохождения игры), и параметр board, в котором хранится размер игрового поля.

Параметр value (количество ходов которое потребовалось пользователю для прохождения игры) вероятно зависит от размера игрового поля (параметра board). Поэтому, перед тем, как приступить к расчёту каких то статистик, например расчёту среднего количества ходов, необходимых для прохождения игры, имеет смысл сгруппировать наши события по размеру игрового поля.

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

SELECT event_name, event_timestamp, user_pseudo_id, param
FROM `firebase-public-project. analytics_153293282.events_20181003`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")

мы от части получим всю необходимую для анализа информацию, но данные по размеру поля, и итоговому количеству ходов будет находиться в разных строках, что затрудняет их анализ.

Одно из решений — просто перегруппировать эти события, посмотрев, какие строки имеют одинаковые user_pseudo_id и event_timestamp. Если две строки имеют одинаковые значения в этих двух столбцах, мы можем быть уверены, что они принадлежат одному и тому же событию. Далее не так и сложно собрать нужные показатели по событию вместе …

SELECT 
  MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
  MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
FROM (
  SELECT event_name, event_timestamp, user_pseudo_id, param
  FROM `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) AS param
  WHERE event_name = "level_complete_quickplay"
  AND (param. key = "value" OR param.key = "board")
) 
GROUP BY user_pseudo_id, event_timestamp

… Теперь мы можем рассчитать среднее количество ходов в зависимости от размера игрового поля.

Задачу мы решили, но такое решение мне кажется не эффективным. Неужели мы действительно собираемся разбить все наши параметры на отдельные строки только для того, чтобы через несколько мгновений снова объединить их вместе? Наверняка должен быть более изящный вариант решения.

SELECT FROM UNNEST в помощь!

К счастью, есть другой вариант решения, конструкция SELECT FROM UNNEST.

Используя конструкцию SELECT FROM UNNEST, вы говорите: «Я хочу применить функцию UNNEST к повторяющейся записи в ее собственной маленькой временной таблице. Далее выбрать одну строку из неё и поместить ее в наши результаты, так же как если бы это было любое другое значение ».

Объяснение приведённое выше звучит сложновато, давайте рассмотрим пример.

Первое, что я собираюсь сделать, это удалить в исходном варианте решения оператор UNNEST, чтобы просмотреть все события прохождения игры.

SELECT event_name, event_timestamp, user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

Затем я запрашиваю столбец value.int_value из развёрнутого массива event_params, где поле key равно value.

SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS score
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

В итоге происходит что-то вроде того, что показывает приведённая ниже анимация. Сначала мы разбиваем массив event_params в его собственную небольшую временную таблицу, фильтруя одну запись, по условию key = "value", а затем забираем полеvalue.int_value.

После чего вы получаете следующий результат:

При использовании конструкции SELECT FROM UNNEST, следует помнить о трех важных моментах:

Во-первых, вы должны убедиться, что вы поместили вызов SELECT FROM UNNEST в круглые скобки. Если этого не сделать, BigQuery выдаст ошибку, т.к. вы используете два оператора SELECT в рамках одного запроса.

Во-вторых, похоже на то, что описанный приём будет работать, только если вы запрашиваете не более одного значения из каждого вызова SELECT FROM UNNEST.

В-третьих, вы можете использовать SELECT FROM UNNEST несколько раз в одном и том же операторе SELECT! Итак, теперь мы можем взять наш предыдущий запрос и добавить в него второй вызовSELECT FROM UNNEST, чтобы получить параметры board и value рядом, в одной строке.

SELECT event_name, event_timestamp, user_pseudo_id,  
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS score,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = "board") AS board_size
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

Приведённый выше запрос вернёт ожидаемый нами результат:

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

SELECT AVG(score) AS average, STDDEV(score) as std_dev, board_size
FROM (  
  SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS score,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = "board") AS board_size
  FROM `firebase-public-project.analytics_153293282.events_20181003`
  WHERE event_name = "level_complete_quickplay"
) 
GROUP BY board_size

SELECT FROM UNNEST также можно использовать для совместного анализа параметров событий со свойствами пользователей.

Например, разработчики Flood-it используют событие trust_virtual_currency, чтобы отслеживать, когда пользователь тратит «дополнительные шаги» в конце раунда. Параметр value помогает отслеживать, сколько шагов они тратят на событие. Мы также отслеживаем количество дополнительных шагов, выполненных пользователем, с помощью свойства initial_extra_steps.

Допустим, мы хотим выяснить, есть ли какая-либо корреляция между тем, сколько шагов пользователю изначально дано, и сколько дополнительных шагов он делает во время события «use extra steps». Для этого нам необходимо проанализировать параметр value вместе с пользовательским свойством initial_extra_steps. Опять же, эти параметры объеденены в повторяющиеся записи, как нам их правильно развернуть?

Теоретически это можно сделать, объединив два вызова UNNEST.

SELECT event_name, event_timestamp, user_pseudo_id, 
  param.value.int_value AS moves_used,
  userprop.value.string_value AS initial_extra_steps
FROM `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST (event_params) as param,
UNNEST (user_properties) as userprop
WHERE event_name = "spend_virtual_currency"
AND param.key = "value"
AND userprop.key = "initial_extra_steps"

Это могло бы сработать, ход выполнения такого запроса будет следующим: сначала мы развернём spend_virtual_currency в отдельные строки, а затем то же самое повторим для каждой из этих строк для каждого записываемого нами свойства пользователя. Это означает, что мы, по сути, умножаем каждую строку в большом наборе данных запроса на (количество параметров * количество свойств пользователя). Очень быстро такой запрос может стать достаточно дорогой операцией!

Возможно, BigQuery творит чудеса под капотом, для предварительной оптимизации подобных запросов, но, учитывая, то, как разработчики BigQuery заметно вздрагивали каждый раз, когда я упоминал многократное использование UNNEST в рамках одного запроса, я в этой оптимизации не очень уверен.

Вновь нам на помощь приходит SELECT FROM UNNEST. Сначала я получу значение нашего параметра value, как в нашем первом примере.

SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS steps_used
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "spend_virtual_currency"

Затем я могу получить значение нашего пользовательского свойства. (Обратите внимание, что это значение хранится в виде строки, поэтому я конвертирую его в целое число) .

SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value. int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS steps_used,
  CAST(
    (SELECT value.string_value FROM UNNEST(user_properties) 
     WHERE key = "initial_extra_steps") 
   AS int64) AS initial_steps
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "spend_virtual_currency"

… Теперь у меня есть все необходимые данные в одной строке!

Этот код не только более эффективен, но и прост для понимания.

Теперь не сложно проанализировать выбранные нами параметры. Мы можем посмотрет, каково среднее значение используемых дополнительных шагов для каждого типа свойств пользователя…

SELECT AVG(steps_used) AS average_steps_used, initial_steps 
FROM (
  SELECT event_name, event_timestamp, user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) 
      WHERE key = "value") AS steps_used,
    CAST(
      (SELECT value.string_value FROM UNNEST(user_properties) 
       WHERE key = "initial_extra_steps") 
     AS int64) AS initial_steps
  FROM `firebase-public-project. analytics_153293282.events_20181003`
  WHERE event_name = "spend_virtual_currency"
) 
WHERE initial_steps IS NOT NULL
GROUP BY initial_steps
ORDER BY initial_steps

… Или мы могли бы посмотреть, есть ли какая-то корреляция между этими двумя значениями.

SELECT CORR(steps_used, initial_steps) AS correlation 
FROM (
SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS steps_used,
  CAST(
    (SELECT value.string_value FROM UNNEST(user_properties) 
     WHERE key = "initial_extra_steps") 
   AS int64) AS initial_steps
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "spend_virtual_currency"
) 
WHERE initial_steps IS NOT NULL

Итак, вы можете использовать UNNEST и SELECT FROM UNNEST для быстрой обработки повторяющихся записей, которые Google Analytics для Firebase любит использовать в своей схеме BigQuery. И, как оказалось, это те же самые повторяющиеся записи, которые отображаются в данных Crashlytics и Cloud Messaging. Поэтому я рекомендую потратить время на то, чтобы привыкнуть к этим методам, т.к. они заметно облегчат работу со сложными структурами данных.

Использование выборки по атрибутам—ArcMap | Документация

• Шаги для применения выборки по атрибутам
• Опции отображения полей в диалоговом окне Выбрать по атрибуту

Один из методов, который можно использовать для выборки объектов в слое, — выборка при помощи атрибутивного запроса. Она выполняется при помощи инструмента Выбрать по атрибуту (Select By Attributes), как описано в этом разделе.

Выбрать по атрибуту (Select By Attributes) позволяет вам задать SQL-выражение запроса, которое используется для выбора объектов, удовлетворяющих критерию выборки.

Шаги для применения выборки по атрибутам

1. Нажмите Выборка (Selection) > Выбрать по атрибуту (Select By Attributes), чтобы открыть диалоговое окно Выбрать по атрибуту (Select By Attributes).
2. Выберите слой, для которого будет выполняться выборка.
3. Укажите метод выборки.
4. Введите выражение запроса, используя один из следующих методов:
   • Создайте запрос, используя инструменты построения выражения.
   • Напечатайте запрос в окне выборки.
   • Загрузите выражение, сохраненное на диске.

   Более подробно о синтаксисе запроса см. Построение выражения запроса.

   Подсказка:

   Щелкните Уникальные значения (Unique Values), чтобы видеть значения для выбранного поля при создании выражения запроса. Можно ввести значение Перейти (Go To) в поле ввода, чтобы быстро перемещаться по этому списку.

5. Подтвердите выражение запроса, нажав Проверить (Verify).
6. Нажмите OK или Применить (Apply), чтобы выполнить выражение выборки и работать с ее результатами.
7. Дополнительно, перед тем, как закрыть диалоговое окно, можно сохранить свое выражение запроса для дальнейшего повторного применения.

Опции отображения полей в диалоговом окне Выбрать по атрибуту

Кнопка в правой части диалогового окна Выбрать по атрибуту (Select By Attributes) позволяет указать, как будут перечислены поля. Отображение псевдонимов полей в списке может быть очень полезным, так как они делают более понятными исходные имена полей и легче управляются, чем длинные названия полей ArcSDE.

Использование псевдонимов полей в данном диалоговом окне не повлияет на синтаксис выражения. Псевдонимы поля не отражаются и не поддерживаются в самом выражении. Если вы дважды щелкнете на псевдониме поля, поле добавится в выражение со своим реальным названием и соответствующими разделителями [ ] поля. В примере ниже в списке показан псевдоним Depth last recorded (cm), который был добавлен в выражение как имя поля DEPTH_BURI.

Другие опции этого меню позволяют выбрать, как поля будут отсортированы в списке. По умолчанию, поля перечислены в том порядке, в каком они находятся в источнике данных. Выбор опций Сортировать по возрастанию (Sort Ascending) или Сортировать по убыванию (Sort Descending) может упростить определение расположения нужного вам поля.

Связанные разделы

sql — Используйте GROUP BY с оператором SELECT CASE WHEN

Задавать вопрос

спросил
1 год, 10 месяцев назад

Изменено
1 год, 10 месяцев назад

Просмотрено
1к раз

У меня есть таблица вида:

 ДАТА_ЗАКАЗА | ЗАКАЗ_ID | СУММА ЗАКАЗА
2020-11-07 | 1 | 40
2020-11-07 | 1 | 60
2021-04-01 | 2 | 100
 

С 01. 07.2020 по 31.12.2020 налог с продаж был снижен с 19% до 16% из-за Covid.

Теперь мне нужно узнать общую сумму по ORDER_ID. Результат должен быть:

 ORDER_ID | СУММА ЗАКАЗА
1 | 116
2 | 119
 

Мой код на данный момент:

 ВЫБЕРИТЕ СЛУЧАЙ, КОГДА ORDER_DATE МЕЖДУ «2020-07-01» И «2020-12-31»
                ТОГДА СУММА(СУММА_ЗАКАЗА) * 1,16
            КОГДА (ДАТА_ЗАКАЗА < '01.07.2020' ИЛИ ​​ДАТА_ЗАКАЗА > '31.12.2020')
                ТОГДА СУММА(ORDER_AMOUNT) * 1,19ИНАЧЕ 0
       ЗАКАНЧИВАТЬ КАК "сумма брутто"
--
ИЗ my_dwh_table
--
ГРУППА ПО
        СЛУЧАЙ, КОГДА ORDER_DATE МЕЖДУ "2020-07-01" И "2020-12-31"
                ТОГДА СУММА(СУММА_ЗАКАЗА) * 1,16
            КОГДА (ДАТА_ЗАКАЗА < '01.07.2020' ИЛИ ​​ДАТА_ЗАКАЗА > '31.12.2020')
                ТОГДА СУММА(ORDER_AMOUNT) * 1,19
            ИНАЧЕ 0
        КОНЕЦ
;
 

, но я получаю эту ошибку в DBeaver:

 Ошибка SQL [42000]: установить функцию в группе по предложению
 

Как использовать оператор CASE WHEN для расчета различных налогов с помощью GROUP BY для ORDER_ID? Где ошибка в моем коде?

• sql
• оракул
• экзасол

1

Я думаю, вы хотите сформулировать это с помощью CASE в качестве аргумента для SUM() :

 SELECT ORDER_ID,
       SUM(CASE WHEN ORDER_DATE МЕЖДУ '2020-07-01' И '2020-12-31'
                ТО ЗАКАЗ_СУММА * 1,16
                ИНАЧЕ ЗАКАЗ_СУММА * 1,19
           КОНЕЦ) КАК общая_сумма
ИЗ my_dwh_table
СГРУППИРОВАТЬ ПО ЗАКАЗУ_ID;
 

Нет ЗАКАЗ ОТ .

Примечание. Предполагается, что ORDER_DATE никогда не бывает NULL (поэтому нет необходимости в ELSE 0 и что '2020-07-01' является допустимой датой в вашей системе. Обычно я использую D АТЕ '2020-07-01' для константы даты

2

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

Анатомия оператора SELECT. Часть 1: Оператор WITH

Пойдем, юноша. Присядьте у открытого огня; поднесите свои холодные руки к его теплу. Позвольте мне рассказать вам историю. Когда я был молод, мир был проще: мы бродили по равнинам, охотились с копьями, а операторы Oracle Select состояли только из SELECT, FROM и, возможно, WHERE. Сделано

Подойди, юноша. Присядьте у открытого огня; поднесите свои холодные руки к его теплу. Позвольте мне рассказать вам историю. Когда я был молод, мир был проще: мы бродили по равнинам, охотились с копьями, а операторы Oracle Select состояли только из SELECT, FROM и, возможно, WHERE.

Все изменилось.

По правде говоря, вам придется вернуться на 20 лет назад, чтобы найти время, когда оператор SELECT не был швейцарским армейским ножом с многочисленными странными предложениями, выступающими из него — от предложений факторинга подзапросов до иерархических предложений запроса. Цель этой серии статей — пролить свет на некоторые из этих пунктов и помочь разработчикам Oracle, особенно тем, кто закончил мою серию Oracle для начинающих, разобраться с ними.

Предложение With

Предложение With — или, если вы предпочитаете модные слова, предложение факторинга подзапроса — это (необязательная) часть, выросшая из базового оператора select как часть Oracle 9.2 еще в 2002 году. Предложение WITH позволяет вам, как часть оператора select, присвоить имя подзапросу и использовать его результаты, ссылаясь на это имя.

На первый взгляд, это довольно неприятно. Потому что предложение факторинга подзапроса жестоко изменяет внешний вид запроса, заставляя его больше не начинаться с ключевого слова SELECT.

Преимущество использования предложения WITH заключается в том, что после того, как вы определили свой подзапрос, вы можете впоследствии неоднократно ссылаться на него в своем основном операторе выбора. Oracle оптимизирует ваш подзапрос, разрешая его как встроенное представление или временную таблицу. Кроме того, вопреки интуиции, когда вы распознаете странный синтаксис, предложение WITH действительно упрощает чтение сложных операторов SELECT. Однако начнем с достаточно простого примера:

Приведенный выше оператор SELECT, по сути, создает подзапрос, который получает количество сотрудников по отделам (он называет подзапрос emp_count), а затем вызывает этот подзапрос в предложении FROM основного оператора выбора так, как он вызывает таблица или представление.

Наш запрос, как я и обещал, достаточно прост, но вы, вероятно, уже начинаете понимать, как с помощью предложения WITH можно группировать сложные подзапросы, оставляя основной оператор SELECT относительно простым.

Но прежде чем мы рискнем спуститься в эту кроличью нору, давайте вернемся к тому, что я сказал ранее: вы можете многократно ссылаться на свой подзапрос. Это имеет смысл, если немного подумать. В конце концов, это временная таблица, и операторы SELECT могут ссылаться на одну и ту же таблицу более одного раза. Я покажу вам, что я имею в виду.

Эта возможность ссылаться на наш именованный подзапрос более одного раза поднимает предложение with над обычными встроенными подзапросами. Если мы хотим воспроизвести приведенный выше запрос, используя встроенные подзапросы, решение будет длиннее и уродливее. Предложение with помогает нам со сложностью.

Действительно, используя тот факт, что вы также можете определить несколько подзапросов, вы можете убрать большую часть сложности, оставив простой основной оператор выбора.

Подсказки оптимизатора

Как я уже говорил ранее, Oracle во время выполнения решает, обрабатывать ли ваши подзапросы как встроенные представления или как временные таблицы. Однако вы можете подтолкнуть его в нужном вам направлении, воспользовавшись парой советов оптимизатора. Подсказка оптимизатора MATERIALIZE указывает Oracle рассматривать ваш подзапрос как временную таблицу; подсказка INLINE, наоборот, говорит ему рассматривать его как встроенное представление.

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

Я встречал операторы SELECT с предложением WITH, которые растягивались на многие сотни строк. В основном они бегают как трехногие собаки, так что нет, я их не защищаю. Именно в этих ситуациях вы можете серьезно рассмотреть подсказки оптимизатора.

Функции

С выпуском версии 12C в 2014 году предложение WITH научилось новому трюку: функциям. Правильно, теперь вы можете создать функцию в предложении WITH и использовать ее в теле основного оператора выбора. (На самом деле новый забавный трюк — это не только функции; вы также можете создавать процедуры, хотя я не понимаю, зачем вам это нужно, поскольку вы не можете вызывать процедуру в операторе SELECT.)

Вот синтаксис: