#240: Data Characterization queries for phase 1 CDM V1 compliance
-------------------------------------------------+-------------------------
Reporter: campbell | Owner: huhickman
Type: task | Status: assigned
Priority: major | Milestone: drn-
Component: data-stds | query-1
Keywords: cdm-etl CDM Compliance data-quality | Resolution:
Blocking: | Blocked By:
-------------------------------------------------+-------------------------
Comment (by huhickman):
I've rewritten the wt query (exactly same structure as above). It now
returns in 7 seconds instead of NEVER.
Removed the function and used a couple of with clauses.....
Now to see what they say about the revised queries.
{{{
#!sql
with
valid_weights as
(select count(*) as TotalValidWt from Vital where wt is not null),
weights_with_rows as
(
SELECT ROW_NUMBER() OVER (ORDER BY wt desc) AS rowno, wt FROM Vital WHERE
wt IS NOT NULL
)
SELECT vit_l3_wt_dist FROM
(SELECT 0 AS position, '"Statistic","WT"' AS vit_l3_wt_dist FROM dual
UNION
SELECT 1 AS position, '"' || Statistic || '",' || CAST(WT AS
VARCHAR(15)) FROM
(SELECT 'MIN' AS Statistic, MIN(wt) AS WT FROM Vital WHERE wt IS
NOT NULL
UNION
SELECT 'MAX' AS Statistic, MAX(wt) AS WT FROM
Vital WHERE wt IS NOT NULL
UNION
SELECT 'MEAN' AS Statistic, SUM(wt)/COUNT(wt) AS
WT FROM Vital WHERE wt IS NOT NULL
UNION
-- Median is calculated by sorting all
records by wt AND getting the wt value of the midpoint of all records
select 'MEDIAN' As Statistic, wwr.wt as wt from
weights_with_rows wwr cross join valid_weights vw
where wwr.rowno = CAST(vw.TotalValidWt/2 as int) and WT is not null
UNION
SELECT 'NULL or missing' AS Statistic, COUNT(*) AS
wt FROM Vital WHERE wt IS NULL
UNION
SELECT 'record_n' AS Statistic, COUNT(*) AS wt
FROM Vital WHERE wt IS NOT NULL
)csv
ORDER BY position,vit_l3_wt_dist
)m;
--
Ticket URL:
<http://informatics.gpcnetwork.org/trac/Project/ticket/240#comment:21>
gpc-informatics <http://informatics.gpcnetwork.org/>
Greater Plains Network - Informatics
_______________________________________________
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
