gpt4 book ai didi

sql - 甲骨文 SQL : Most efficient way to calculate Z-score of grouped data

转载 作者:行者123 更新时间:2023-12-04 19:29:23 26 4
gpt4 key购买 nike

我有两个表:数据

DATA_ID  |  SAMPLE_ID  |  ASSAY_ID  |  SIGNAL
101 | 201 | 301 | 2.87964
102 | 201 | 302 | 7.64623
103 | 202 | 301 | 1.98473
...

样本:

SAMPLE_ID  |  SAMPLE_NAME  |  CATEGORY
201 | SAMP0001 | CAT A
202 | SAMP0002 | CAT B
203 | SAMP0003 | CAT A
...

SAMPLES 中大约有 20,000 行。对于每个示例,DATA 中大约有 40,000 行。每个 ASSAY_IDDATA 中的每个样本中恰好出现一次。我需要在 SAMPLE 中提取样本子集,并为 DATA 中的每个信号值计算标准/z 分数值,按 ASSAY_ID 分组>。我正在尝试创建一个将被重复调用的存储过程,它将接受单个 ASSAY_ID 值并返回所有的 SAMPLE_IDZSCORE 对预定义样本子集中的样本数。

给定一组样本信号值 (X = [3.21, 4.56, 1.12, ..]),在这种情况下,标准/z 分数计算为

(X[i] - median(X))/(K * MAD)

其中 K 是比例因子,等于 1.4826,MAD 是中位数调整偏差,等于:

median(|X[i]-median(X)|)

明白了吗?好 :) 现在,使用 SQL 查询执行此计算的最有效方法是什么?执行时间是关键,因为 DATA 中有近十亿行,而且几乎每个 SIGNAL 值都需要计算 z 分数。

这是迄今为止我能想到的最好的查询:

WITH BASE AS (
SELECT
S.SAMPLE_ID,
D.SIGNAL
FROM
DATA D
JOIN SAMPLES S
ON D.SAMPLE_ID = S.SAMPLE_ID
WHERE
S.CATEGORY IN ('CAT A', 'CAT B')
AND D.ASSAY_ID = 12345
AND S.SAMPLE_NAME NOT IN ('SAMP0003', 'SAMP0005', 'SAMP0008')
)
SELECT
A.SAMPLE_ID,
(A.SIGNAL-B.MED)/(1.4826*C.MAD) AS ZSCORE
FROM
BASE A,
(
SELECT MEDIAN(X.SIGNAL) AS MED
FROM BASE X
) B,
(
SELECT MEDIAN(ABS(Y.SIGNAL-YY.MED)) AS MAD
FROM BASE Y,
(SELECT MEDIAN(SIGNAL) AS MED FROM BASE) YY
) C

是否有更有效的方法来执行此查询?

奖励问题:我能否编写一个 SQL 查询,在单次执行中为每个 ASSAY_ID 执行此计算?

最佳答案

你能看看吗:

SELECT ASSAY_ID, SAMPLE_ID, 
(SIGNAL - MED)/(1.4826F * MAD) AS ZSCORE
FROM (
SELECT ASSAY_ID, SAMPLE_ID, SIGNAL, MED,
MEDIAN(ABS(SIGNAL - MED)) OVER (PARTITION BY ASSAY_ID) AS MAD
FROM (
SELECT ASSAY_ID, SAMPLE_ID, SIGNAL,
MEDIAN(SIGNAL) OVER (PARTITION BY ASSAY_ID) AS MED
FROM DATA D
JOIN SAMPLES S USING (SAMPLE_ID)
WHERE S.CATEGORY IN ('CAT A', 'CAT B')
AND S.SAMPLE_NAME NOT IN ('SAMP0003', 'SAMP0005', 'SAMP0008')
AND D.ASSAY_ID = 301
)
);

是否正确?它更快吗?如果是,只需删除 奖励问题AND D.ASSAY_ID = 301 子句:-)

在物理方面,我会研究信号的数据类型(BINARY_FLOATBINARY_DOUBLE 据推测比 NUMBER 快)。而且,如果这是一个选项,我会尝试在物理上将化验与分区并置。

关于sql - 甲骨文 SQL : Most efficient way to calculate Z-score of grouped data,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14267522/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com