Блог

Главная - Вопросы по программированию - Повторное использование агрегированных вычислений в разных частях инструкции CASE

Повторное использование агрегированных вычислений в разных частях инструкции CASE

#google-bigquery #case #aggregate-functions

#google-bigquery #случай #агрегатные функции

Вопрос:

У меня есть таблица myTable с таблицей с одним столбцом, содержащая целое число, представляющее однобайтовое значение, подобное этому:

 status_code
-----------
(single bit values:)
1
2
4
8
(multiple bit values:)
...
12
3
etc.

Я выбираю общий «статус» на основе общей доли битов, которые соответствуют определенному значению, используя логику, которая выглядит следующим образом. Как вы можете видеть, есть небольшое повторение, когда я повторно выполняю некоторые идентичные вычисления:

 select 
       (case when (sum(case when status_code >> 4 = 1 then 1 else 0 end) / count(*))   
                  (sum(case when status_code >> 3 = 1 then 1 else 0 end) / count(*)) > 0.75
             then 0
       else 
          case when (sum(case when status_code >> 4 = 1 then 1 else 0 end) / count(*))   
                    (sum(case when status_code >> 3 = 1 then 1 else 0 end) / count(*))  
                    (sum(case when status_code >> 2 = 1 then 1 else 0 end) / count(*)) > 0.75
               then 1
          else 
               case when (sum(case when status_code >> 4 = 1 then 1 else 0 end) / count(*))   
                         (sum(case when status_code >> 3 = 1 then 1 else 0 end) / count(*))  
                         (sum(case when status_code >> 2 = 1 then 1 else 0 end) / count(*))  
                         (sum(case when status_code >> 1 = 1 then 1 else 0 end) / count(*)) > 0.75
                    then 2
               else 
                 3
               end
          end
        end) as status 
from myTable

Как я могу упростить запрос (и, возможно, сделать его более производительным), повторно используя вычисленные пропорции каждого отдельного статуса? Например, как я мог бы использовать псевдоним следующего и повторно использовать его в разных частях инструкции case?

 (sum(case when status_code >> 4 = 1 then 1 else 0 end) / count(*))

Возможно ли это вообще?

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

1. Знаете ли вы о битовой функции bigquery? cloud.google.com/bigquery/docs/reference/standard-sql /…

2. Спасибо @Tamier Klein. Это подсчитывает количество битов, представленных в каждом байте, тогда как я подсчитываю пропорции, которые были установлены для каждого бита, во всех записях

Ответ №1:

Как я могу упростить запрос?

Я думаю, что ниже приведен один из вариантов 

 #standardSQL
CREATE TEMP FUNCTION f(code INT64, bits INT64) AS (
  IF(code >> bits = 1, 1, 0)
);
SELECT (CASE 
  WHEN (SUM(f(status_code, 4))   
    SUM(f(status_code, 3))) / COUNT(*) > 0.75 THEN 0
  WHEN (SUM(f(status_code, 4))    
    SUM(f(status_code, 3))   
    SUM(f(status_code, 2))) / COUNT(*) > 0.75 THEN 1
  WHEN (SUM(f(status_code, 4))   
    SUM(f(status_code, 3))  
    SUM(f(status_code, 2))  
    SUM(f(status_code, 1))) / COUNT(*) > 0.75 THEN 2
  ELSE 3
  END) AS status 
FROM `project.dataset.table`

или дальнейший «рефакторинг» дает: 

 #standardSQL
CREATE TEMP FUNCTION f(code INT64, bits INT64) AS (
  IF(code >> bits = 1, 1, 0)
);
SELECT 
  (CASE 
    WHEN SUM(f(status_code, 4)   f(status_code, 3)) / COUNT(*) > 0.75 THEN 0
    WHEN SUM(f(status_code, 4)   f(status_code, 3)   f(status_code, 2)) / COUNT(*) > 0.75 THEN 1
    WHEN SUM(f(status_code, 4)   f(status_code, 3)   f(status_code, 2)   f(status_code, 1)) / COUNT(*) > 0.75 THEN 2
    ELSE 3
  END) AS status 
FROM `project.dataset.table`

и, вероятно, сделать его более производительным…

Итак, наконец 

 #standardSQL
CREATE TEMP FUNCTION f(code INT64, bits INT64) AS (
  IF(code >> bits = 1, 1, 0)
);
SELECT 
  (CASE 
    WHEN f4   f3 > 0.75 THEN 0
    WHEN f4   f3   f2 > 0.75 THEN 1
    WHEN f4   f3   f2   f1 > 0.75 THEN 2
    ELSE 3
  END) AS status 
FROM (
  SELECT 
    SUM(f(status_code, 4))/ COUNT(*) f4, 
    SUM(f(status_code, 3))/ COUNT(*) f3, 
    SUM(f(status_code, 2))/ COUNT(*) f2, 
    SUM(f(status_code, 1))/ COUNT(*) f1 
  FROM `project.dataset.table`
)