Блог

Мне нужно обновить нулевые значения столбца в таблице для каждой категории на основе процента ненулевых значений. В следующей таблице показаны нулевые значения для определенной категории —

В столбце есть только два типа значений. Процент типов, основанных на строках, равен —

Количество строк с нулевыми значениями равно 7, мне нужно случайным образом заполнить нулевые значения на основе процентной доли ненулевых значений, как показано ниже — 38% (CV) 7 = 3, 63% (NCV) 7 = 4

Если вы хотите динамически вычислять «НУЛЕВУЮ скорость», одним из способов сделать это может быть:

 with pcts as (
  select 
    (select count(*)::numeric from the_table where type = 'cv') / (select count(*) from the_table where type is not null) as cv_pct,
    (select count(*)::numeric from the_table where type = 'ncv') / (select count(*) from the_table where type is not null) as ncv_pct,
    (select count(*) from the_table where type is null) as null_count
), calc as (
  select d.ctid, 
         p.cv_pct, 
         p.ncv_pct, 
         row_number() over () as rn, 
         case 
           when row_number() over () <= round(null_count * p.cv_pct) then 'cv'
           else 'ncv'
         end as new_type
  from the_table d
    cross join pcts p
  where type is null
)
update the_table t
  set type = c.new_type
from calc c
where t.ctid = c.ctid
  

Первый CTE вычисляет процентное соотношение каждого типа и общее количество нулевых значений (теоретически процентное соотношение типа NCV на самом деле не требуется, но я включил его для полноты)

Затем второй вычисляет для каждой строки, какой новый тип следует использовать. Это делается путем умножения «текущего» номера строки на ожидаемый процент (выражение CASE)

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

Я бы не удивился, если бы существовал более короткий и эффективный способ сделать это, но пока я не могу придумать лучшей альтернативы.

Онлайн-пример

Если вы используете PG11 или более позднюю версию, вы можете использовать groups фрейм, чтобы сделать это за то, что должно быть близко к одному проходу (за исключением изменения порядка вывода при сортировке по tid ) с помощью оконных функций:

 select tid, category, id, type, 
       case 
         when type is not null then type
         when round(
                (count(*) over (partition by category
                                  order by type nulls last
                         groups between 2 preceding
                                    and 2 preceding))::numeric / 
                coalesce(
                  nullif(
                    count(*) over (partition by category
                                       order by type nulls last
                                      groups 2 preceding 
                                     exclude group), 0), 1
                ) * 
                count(*) over (partition by category
                                   order by type nulls last
                                  groups current row) 
              ) >= row_number() over (partition by category, type
                                          order by tid) 
              then
                first_value(type) over (partition by category
                                            order by type nulls last
                                   groups between 2 preceding
                                              and 2 preceding)
              else
                first_value(type) over (partition by category
                                            order by type nulls last
                                           groups 1 preceding
                                           exclude group)
        end as extended_type
  from cv_ncv 
 order by tid;
  

Здесь работает скрипка.