Я хочу создать флаг в Bigquery, который будет возвращать 1, если истинно, и 0, если ложно. Оператор отлично работает, когда он должен возвращать значение "else", равное 0. Однако, когда он удовлетворяет условию, он возвращает две строки, содержащие как 1, так и 0. Почему это происходит?
Ниже приведен используемый код:
table AS(
SELECT
id,
month,
ROUND((text/(month_days/7)), 2) AS value
FROM (SELECT id, extract(month FROM date) AS month,
(32 - EXTRACT(DAY FROM DATE_ADD(DATE_TRUNC(DATE(date), MONTH), INTERVAL 31 DAY))) AS month_days,
sum(text_sent) AS text
FROM table1
WHERE date BETWEEN '2020-01-01 00:00:00 UTC' AND '2020-06-30 00:00:00 UTC'
GROUP BY 1,2,3)),
table_flag AS(
SELECT
id,
CASE
WHEN month = 1 AND value > 100 THEN 1
WHEN month = 2 AND value > 150 THEN 1
WHEN month = 3 AND value > 130 THEN 1
WHEN month = 4 AND value > 200 THEN 1
WHEN month = 5 AND value > 235 THEN 1
WHEN month = 6 AND value > 125 THEN 1
WHEN month = 7 AND value > 324 THEN 1
WHEN month = 8 AND value > 160 THEN 1
WHEN month = 9 AND value > 350 THEN 1
WHEN month = 10 AND value > 80 THEN 1
WHEN month = 11 AND value > 245 THEN 1
ELSE 0
END AS value_flag
FROM
table)
SELECT
t.id,
t.value,
t.month,
tf.value_flag
FROM
table t
LEFT JOIN
table_flag tf
ON
t.id = tf.id
WHERE t.id IS NOT NULL
GROUP BY 1,2,3,4
ORDER BY 1
Я также пробовал вложенный IF, но это тоже не работает:
SELECT DISTINCT(id),
(IF((month = 1 AND value > 100), 1,
(IF((month = 2 AND value > 150), 1,
(IF((month = 3 AND value > 130), 1,
(IF((month = 4 AND value > 200), 1,
(IF((month = 5 AND value > 235), 1,
(IF((month = 6 AND value > 125), 1,
(IF((month = 7 AND value > 324), 1,
(IF((month = 8 AND value > 160), 1,
(IF((month = 9 AND value > 350), 1,
(IF((month = 10 AND value > 80), 1,
(IF((month = 11 AND value > 245), 1,0))))))))))))))))))))))
AS value_flag
FROM table)
Вот как результат выглядит прямо сейчас (это НЕ то, что я хочу):
введите здесь описание изображения
Выход совершенно неверный. Пожалуйста, предложите альтернативный метод (если есть), чтобы сделать это.
P.S.: Это мой первый вопрос здесь, пожалуйста, дайте мне знать, если потребуется какая-либо другая информация. Заранее спасибо за помощь!
И table
, и table_flag
имеют несколько строк с одинаковыми id
. BigQuery для каждой строки в table
находит несколько строк в table_flag
. Чтобы удалить дубликаты, мы могли бы добавить month
к table_flag
и к предложению ON
. Но нам на самом деле не нужен последний LEFT JOIN
. Попробуй это:
WITH table AS(
SELECT
id,
month,
ROUND((text/(month_days/7)), 2) AS value
FROM (
SELECT
id,
extract(month FROM date) AS month,
(32 - EXTRACT(DAY FROM DATE_ADD(DATE_TRUNC(DATE(date), MONTH), INTERVAL 31 DAY))) AS month_days,
sum(text_sent) AS text
FROM table1
WHERE
date BETWEEN '2020-01-01 00:00:00 UTC' AND '2020-06-30 00:00:00 UTC'
AND id IS NOT NULL
GROUP BY 1,2,3
)
)
SELECT
id,
value,
month,
CASE
WHEN month = 1 AND value > 100 THEN 1
WHEN month = 2 AND value > 150 THEN 1
WHEN month = 3 AND value > 130 THEN 1
WHEN month = 4 AND value > 200 THEN 1
WHEN month = 5 AND value > 235 THEN 1
WHEN month = 6 AND value > 125 THEN 1
WHEN month = 7 AND value > 324 THEN 1
WHEN month = 8 AND value > 160 THEN 1
WHEN month = 9 AND value > 350 THEN 1
WHEN month = 10 AND value > 80 THEN 1
WHEN month = 11 AND value > 245 THEN 1
ELSE 0
END AS value_flag
FROM table
ORDER BY 1
или это:
SELECT
id,
month,
ROUND((text/(month_days/7)), 2) AS value,
CASE
WHEN month = 1 AND ROUND((text/(month_days/7)), 2) > 100 THEN 1
WHEN month = 2 AND ROUND((text/(month_days/7)), 2) > 150 THEN 1
WHEN month = 3 AND ROUND((text/(month_days/7)), 2) > 130 THEN 1
WHEN month = 4 AND ROUND((text/(month_days/7)), 2) > 200 THEN 1
WHEN month = 5 AND ROUND((text/(month_days/7)), 2) > 235 THEN 1
WHEN month = 6 AND ROUND((text/(month_days/7)), 2) > 125 THEN 1
WHEN month = 7 AND ROUND((text/(month_days/7)), 2) > 324 THEN 1
WHEN month = 8 AND ROUND((text/(month_days/7)), 2) > 160 THEN 1
WHEN month = 9 AND ROUND((text/(month_days/7)), 2) > 350 THEN 1
WHEN month = 10 AND ROUND((text/(month_days/7)), 2) > 80 THEN 1
WHEN month = 11 AND ROUND((text/(month_days/7)), 2) > 245 THEN 1
ELSE 0
END AS value_flag
FROM (
SELECT
id,
extract(month FROM date) AS month,
(32 - EXTRACT(DAY FROM DATE_ADD(DATE_TRUNC(DATE(date), MONTH), INTERVAL 31 DAY))) AS month_days,
sum(text_sent) AS text
FROM table1
WHERE
date BETWEEN '2020-01-01 00:00:00 UTC' AND '2020-06-30 00:00:00 UTC'
AND id IS NOT NULL
GROUP BY 1,2,3
)
ORDER BY 1
Обновлен ответ
Это сработало! У меня было соединение, потому что мне нужно было добавить еще одну таблицу, и, вероятно, это было причиной проблемы. Огромное спасибо за помощь!
Я отредактировал свой вопрос, пожалуйста, посмотрите!