- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有两个表:数据
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_ID
在 DATA
中的每个样本中恰好出现一次。我需要在 SAMPLE
中提取样本子集,并为 DATA
中的每个信号值计算标准/z 分数值,按 ASSAY_ID
分组>。我正在尝试创建一个将被重复调用的存储过程,它将接受单个 ASSAY_ID
值并返回所有的 SAMPLE_ID
和 ZSCORE
对预定义样本子集中的样本数。
给定一组样本信号值 (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_FLOAT
或 BINARY_DOUBLE
据推测比 NUMBER
快)。而且,如果这是一个选项,我会尝试在物理上将化验与分区并置。
关于sql - 甲骨文 SQL : Most efficient way to calculate Z-score of grouped data,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14267522/
我在一个数据集中有一个来自不同来源的姓名列表:一组按 FirstName LastName 组织;另一个有全名。我想查看名字或姓氏是否在全名列中,并创建一个标志。两个问题: 首先,我 u sed th
我一直认为不存在是正确的方法,而不是使用不存在条件。但是,我对我一直使用的查询进行比较,我注意到“Not In”条件的执行实际上似乎更快。任何关于为什么会出现这种情况的见解,或者如果到目前为止我只是做
我需要开发一个 iPad 应用程序,它应该管理两种方向模式(横向和纵向)。 根据 official Apple iOS documentation , 有 2 种方法可以继续。 -第一个包括在收到旋转
我有一个类有 2 个变量成员: class A: fullname = "" email = "" 内存中存储了一个A的列表,现在我需要针对全名或电子邮件进行搜索,搜索需要支持模糊搜索
哪个更有效率?或者它们都同样有效?带星号的行中的底层架构发生了什么? (1) for(int i = m_size; i > index; --i) { int k = normalize(
要检查两个不同日期范围的重叠,{Start1, End1} 和 {Start2, End2} 我正在检查: if ((Start1 = Start2)) { //overlap exists }
这个问题在这里已经有了答案: 关闭 13 年前. Possible Duplicate: Is there a performance difference between i++ and ++i i
前言: 学习ComfyUI是一场持久战, efficiency-nodes-comfyui是提高工作流创造效率的工具,包含效率节点整合工作流中的基础功能,比如Efficient Loader节点相当
我正在编写一个 Java 小程序,并且正在尝试读取一个 220K 行 (9.2 MB) 的文本文件,该文件是用 .jar 归档的。我相信我对文本文件的唯一合理访问是InputStream。为了使用 I
我有一个 Java 应用程序,需要播放一些不同的“声音/连复段”来指示状态。我想知道是否最好将这些记录为音频文件(wav 或任何格式)并使用 Java 音频类播放它们,或者存储 MIDI 数据并使用
就目前情况而言,这个问题不太适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持,但这个问题可能会引发辩论、争论、民意调查或扩展讨论。如果您觉得这个问题可以改进并可能重新开放,visit
很抱歉这个问题太基本了。 目标:这是我的软件程序的输出: 1 590 SC 1.000 LEU2_YEAST 100% 1 590 EC 1.000 LEU2_ECOLI 10
重复使用 $(this) 是否有效,还是将其保存到变量中更好?我已经看到它在很多 jQuery 代码中重复使用,但由于它是对构造函数的调用,我认为它应该不必要地慢,我错了吗? 最佳答案 只是为了好玩
我正在考虑编写一个函数,该函数使用 calloc 创建一个数组来容纳文件中的数据(目前以字符形式)。据我了解,我的两个最明显的选择是读取所有字符以获得所需的总大小,使用 calloc 分配所需的空间,
好的,假设我有如下类: public class KPIObject // Data { get; set; } public string Caption { get; set; } } p
我想对表演者数组进行排序,以便他们按名字的第一个字符进行分组。例如,以下输出中的“A”代表名字以“A”开头的表演者的集合。 [ "A"[Performer,Performer,Performer,Pe
我正在使用此 MySQL 语句来查找社区的平均特性评估。问题是我必须为每个邻域添加一条 SELECT 语句——而且有很多邻域。有没有办法不必指定“RIVER FRONT”或“OLD TOWN”?有没有
我有许多表,大约有四个,我希望连接在一起。为了使我的代码更清晰和可读(对我而言),我希望一次加入所有代码,然后在最后过滤: SELECT f1, f2, ..., fn FROM t1 INNER J
我有以下代码: setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); # statement handle (prevents in
我有一个巨大的数据文件,我只需要这个文件中的特定数据,以后我会经常使用这些数据。那么这两种方法中哪一种更有效: 将此数据保存在全局变量(可能是 LinkedList)中,并在每次需要时使用它们 将它们
我是一名优秀的程序员,十分优秀!