Снежинка SQL | Как суммировать различные значения за один проход?
Я работаю с данными ответов на опросы, где пользователь мог пройти опрос несколько раз. Пользователям назначен «вес», который является целым числом. Я пытаюсь вычислить сумму user_weight по городам, следя за тем, чтобы не учитывать дважды user_weight для пользователей, которые принимали участие в опросе несколько раз.
Используя приведенный здесь пример данных, обратите внимание, что каждый из пользователей проходил опрос дважды.
Я могу рассчитать правильный вес города, используя двухпроходной подход. Ниже приведен код SQL-снежинки с использованием CTE, который сначала устраняет дубликаты кортежей (city, user, user_weight), а затем суммирует user_weight по городам. Этот подход работает, и я получаю правильные веса (150 для Чикаго и 55 для Денвера).
Существует ли однопроходной подход для решения этой проблемы?
create or replace temp table temp as
select 1 as response_id, 1 as user_id, 50 as user_weight, 'chicago' as city
union select 2, 1, 50, 'chicago'
union select 3, 2, 100, 'chicago'
union select 4, 2, 100, 'chicago'
union select 5, 3, 30, 'denver'
union select 6, 3, 30, 'denver'
union select 7, 4, 25, 'denver'
union select 8, 4, 25, 'denver'
;
with base as (
select
distinct
city,
user_id,
user_weight
from
temp
)
select
city,
sum(user_weight) as city_weight
from
base
group by city
;
Я не уверен, что есть хороший способ сделать это без CTE, а Snowflake на самом деле не выполняет эти операции последовательно с 1 или 2 проходами, поэтому я не вижу причин менять то, что у вас есть. row_number и квалификации будут другим способом выполнения отдельной части вашего CTE, а QUALIFY происходит после выбора, поэтому его нельзя использовать в сочетании с GROUP BY, как вам хотелось бы. Может быть какой-то необычный способ сделать это без CTE, но это, скорее всего, приведет к снижению производительности.
может ли один и тот же пользователь вводить несколько значений веса при каждой отправке опроса. если да, то как вы хотите с этим справиться (взять первое, последнее или среднее значение)?
@HaleemurAli значения веса не меняются для пользователя в каждой отправке опроса.
Ответы 1
Вы можете выполнить дедупликацию с помощью group by, затем запустить оконную sum поверх этого, а затем добавить distinct, чтобы свернуть окно. Тем не менее, то, что у вас есть, совершенно нормально и более понятно.
select distinct
city,
sum(user_weight) over (partition by city) as city_weight
from temp
group by city, user_id, user_weight;
отличное решение. возможно, стоит явно указать фрейм как неограниченный предшествующий/следующий, поскольку фрейм окна по умолчанию может варьироваться в зависимости от того, указывает ли запрос порядок по предложению.
@HaleemurAli Для функций, не основанных на ранге, таких как sum, поведение по умолчанию предсказуемо, интуитивно понятно и согласовано во всех базах данных, поэтому я предпочитаю оставлять определение фрейма, если это не требуется из-за проблемы. Я бы согласился с вашим предложением о ранговых функциях, особенно first_value() и least_value().
Возможно ли объединить оконную функцию row_number с QUALIFY?