Блог

Инженер предоставил мне некоторые данные, и ей интересно, могу ли я обобщить ее данные, чтобы они были в меньшем количестве строк, если не в одной строке. Ссылка github на некоторые данные показана ниже, и, как вы можете видеть, она полна избыточности и нулевых значений. Я пробовал простую группировку, но это не сработало. Сейчас я создаю несколько представлений, в каждом из которых добавляется столбец. При создании каждого представления я указываю, что каждый столбец не равен нулю, чтобы я мог попытаться уменьшить нулевые строки. Однако я все еще получаю избыточные идентификаторы образцов, и значения столбцов находятся там, где их не должно быть из-за этой избыточности. Вот что я делаю для одного из представлений:

 create or replace view leslie_6 as
select distinct s.SMPL_ID,s.EFFECTIVENESS_PNS_SCORE_,
s.PNS_BLANK,s.pns_nacl,
s.settable_solids,
t.perc_pass_10m
from leslie_5 s right join leslietable t on s.SMPL_ID = t.smpl_id
where s.EFFECTIVENESS_PNS_SCORE_ is not null and
s.PNS_BLANK is not null and 
s.pns_nacl is not null and
s.settable_solids is not null and 
t.perc_pass_10m is not null
group by s.SMPL_ID,s.EFFECTIVENESS_PNS_SCORE_,s.PNS_BLANK,
s.pns_nacl,s.settable_solids,t.perc_pass_10m
order by s.SMPL_ID
  

Другая проблема заключается в том, что идентификатор образца — это самое близкое, что у меня есть, к ключу. Я надеялся, что смогу достаточно хорошо разбить данные, чтобы SampleID мог быть ключом, но на самом деле это не работает. Длина таблицы превышает 7500 строк, и в ней беспорядок.

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

https://github.com/thomasawolff/verification_text_data/blob/master/Lydia query deicers 20161005_sample.csv

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

Но я мог бы использовать что-то вроде:

 select max(to_number(regexp_substr()) 
  

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

 ---*** Takes the max or the value of the only entry in a column
create or replace view maxdataset as
select t.smpl_id,
t.matl_cd,t.geog_area_t,
max(t.assay) as assay,
max(t.effectiveness_pns_score_) as effectiveness_pns_score,
max(t.pns_blank) as pns_blank,
max(t.pns_nacl) as pns_nacl,
max(t.settable_solids) as settable_solids,
max(t.perc_pass_10m) as perc_pass_10m,
max(t.ph) as ph,
max(t.as_) as as_,
max(t.ba) as ba,
max(t.cd) as cd,
max(t.cr) as cr,
max(t.cu) as cu,
max(t.pb) as pb,
max(t.hg) as hg,
max(t.se) as se,
max(t.zn) as zn,
max(t.cn) as cn,
max(t.p) as p,
max(t.s) as s,
max(t.sulfate) as sulfate,
max(t.phosphate) as phosphate,
max(t.k) as k,
max(t.ca) as ca,
min(t.mg) as mg,
max(t.nitrite) as nitrite,
max(t.nitrate) as nitrate,
max(t.chloride) as chloride
from leslietable t
group by t.smpl_id,t.matl_cd,t.geog_area_t
;
---*** Removes just test ID rows with columns having more than one entry per test ID
create or replace view doubleEffect_short as
select t.smpl_id
from LESLIETABLE t inner join LESLIETABLE s
on t.smpl_id = s.smpl_id where 
(t.effectiveness_pns_score_ <> s.effectiveness_pns_score_) or 
(t.pns_blank <> s.pns_blank) or (t.pns_nacl <> s.pns_nacl) or
(t.settable_solids <> s.settable_solids) or
(t.perc_pass_10m <> s.perc_pass_10m) or
(t.ph <> s.ph) or (t.as_ <> s.as_) or
(t.as_ <> s.as_) or (t.ba <> s.ba) or
(t.cd <> s.cd) or (t.cr <> s.cr) or
(t.cu <> s.cu) or (t.pb <> s.pb) or
(t.hg <> s.hg) or (t.se <> s.se) or
(t.zn <> s.zn) or (t.cn <> s.cn) or
(t.p <> s.p) or (t.sulfate <> s.sulfate) or
(t.phosphate <> s.phosphate) or
(t.k <> s.k) or (t.ca <> s.ca) or
(t.mg <> s.mg) or (t.nitrite <> s.nitrite) or
(t.nitrate <> s.nitrate) or (t.chloride <> s.chloride)
group by t.smpl_id
order by t.smpl_id
;
---*** Outputs all columns from max data set having more than one value per column
create or replace view final_data_sifter as
select  t.smpl_id,t.matl_cd,t.geog_area_t,t.assay,t.effectiveness_pns_score,
t.pns_blank,t.pns_nacl,t.settable_solids,t.perc_pass_10m,t.ph,t.as_,t.ba,t.cd,
t.cr,t.cu,t.pb,t.hg,t.se,t.zn,t.cn,t.p,t.s,t.sulfate,t.phosphate,t.k,t.ca,t.mg,
t.nitrite,t.nitrate,t.chloride 
from maxdataset t join doubleeffect_short s
on t.smpl_id = s.smpl_id
;
---*** Sutracts rows with multiple values per column from max data set 
create or replace view finalDataset_incomplete as
select * from maxdataset t
minus
select * from final_data_sifter