我有一个奇怪的,我不知道这是我的语法(看起来很简单)还是一个bug(或者只是不支持)。
这是我的查询,工作,但不必要的慢:
UPDATE table1
SET table1column1 =
(SELECT COUNT(DISTINCT table2column1) FROM table2view WHERE table2column1 <= (SELECT table2column1 FROM table2 WHERE table2.id = table1.id) )
/
(SELECT COUNT(DISTINCT table2column1) FROM table2)
+ (SELECT COUNT(DISTINCT table2column2) FROM table2view WHERE table2column2 <= (SELECT table2column2 FROM table2 WHERE table2.id = table1.id) )
/
(SELECT COUNT(DISTINCT table2column2) FROM table2)
+ (SELECT COUNT(DISTINCT table2column3) FROM table2view WHERE table2column3 <= (SELECT table2column3 FROM table2 WHERE table2.id = table1.id) )
/ (SELECT COUNT(DISTINCT table2column3) FROM table2);
它只是三个百分位数(table2columnn1, table2column2和table2column3)的和,删除了重复项。
这就是奇怪的地方。我必须使用一个视图来处理WHERE
的子查询,否则它只会UPDATE
table1
的第一行,并将其余行table1column1
设置为0
。那个table2view
是table2
的精确副本。是的,奇怪。
如果我不使用DISTINCT
,我可以做到没有视图。明白了吗?注意:我必须有DISTINCT
,因为我有很多重复。
我试着让SELECT
只从视图,但这减慢了它的速度。
有没有人知道问题是什么,最好的方法来重新工作这个查询,所以它不需要这么长时间?它在TRIGGER
中,更新的数据非常按需。
提前感谢!
我在phpMyAdmin的命令行中测试速度。
我很确定性能下降来自于视图,因为我使用的视图越多,实际表越少,速度就越慢。
当我做一个没有DISTINCT
的,它是闪电。
只适用于视图?
好的,所以我刚刚建立了一个table2
的副本。我首先尝试用副本替换视图来做原始查询。不去。
我尝试用副本而不是视图执行下面的查询。不去。
希望这些常量的引入能更好地展示我要做的事情。
SET @table2column1_distinct_count = (SELECT COUNT(DISTINCT table2column1) FROM table2);
SET @table2column2_distinct_count = (SELECT COUNT(DISTINCT table2column2) FROM table2);
SET @table2column3_distinct_count = (SELECT COUNT(DISTINCT table2column3) FROM table2);
UPDATE table1, table2
SET table1.table1column1 = (SELECT COUNT(DISTINCT table2column1) FROM table2view WHERE table2column1 <= table2.table2column1) / @table2column1_distinct_count
+ (SELECT COUNT(DISTINCT table2column2) FROM table2view WHERE table2column2 <= table2.table2column2) / @table2column2_distinct_count
+ (SELECT COUNT(DISTINCT table2column3) FROM table2view WHERE table2column3 <= table2.table2column3) / @table2column3_distinct_count
WHERE table1.id = table2.id;
再次,当我使用table2
而不是table2view
时,它只正确地更新第一行并设置所有其他行的表1。table1column1 = 0
.
我试图将table1.table1column1
=设置为table2column1
, table2column2
和table2column3
的百分位数与id
的总和。
我通过(计算table2columnX
<=当前table2columnX
的不同值)/(不同table2columnX
s的总数)来计算百分位数。
我使用DISTINCT
来去除过多的重复。
这是视图的SELECT
。这有帮助吗?
CREATE VIEW myTable.table2view AS SELECT
table2.table2column1 AS table2column1,
table2.table2column2 AS table2column2,
table2.table2column2 AS table2column3,
FROM table2
GROUP BY table2.id;
在视图的SELECT
中,GROUP BY
是否有一些特殊的东西使这个工作(我没有看到)?
我可能会说查询很慢,因为当触发器触发时,它会反复访问表。
我不是SQL专家,但我曾尝试使用临时表组合查询。您可以看看它是否有助于加快查询速度。我在下面的代码示例中使用了不同但听起来相似的列名。
EDIT:在我之前的代码中有一个计算错误。现在更新。
SELECT COUNT(id) INTO @no_of_attempts from tb2;
-- DROP TABLE IF EXISTS S1Percentiles;
-- DROP TABLE IF EXISTS S2Percentiles;
-- DROP TABLE IF EXISTS S3Percentiles;
CREATE TEMPORARY TABLE S1Percentiles (
s1 FLOAT NOT NULL,
percentile FLOAT NOT NULL DEFAULT 0.00
);
CREATE TEMPORARY TABLE S2Percentiles (
s2 FLOAT NOT NULL,
percentile FLOAT NOT NULL DEFAULT 0.00
);
CREATE TEMPORARY TABLE S3Percentiles (
s3 FLOAT NOT NULL,
percentile FLOAT NOT NULL DEFAULT 0.00
);
INSERT INTO S1Percentiles (s1, percentile)
SELECT A.s1, ((COUNT(B.s1)/@no_of_attempts)*100)
FROM (SELECT DISTINCT s1 from tb2) A
INNER JOIN tb2 B
ON B.s1 <= A.s1
GROUP BY A.s1;
INSERT INTO S2Percentiles (s2, percentile)
SELECT A.s2, ((COUNT(B.s2)/@no_of_attempts)*100)
FROM (SELECT DISTINCT s2 from tb2) A
INNER JOIN tb2 B
ON B.s2 <= A.s2
GROUP BY A.s2;
INSERT INTO S3Percentiles (s3, percentile)
SELECT A.s3, ((COUNT(B.s3)/@no_of_attempts)*100)
FROM (SELECT DISTINCT s3 from tb2) A
INNER JOIN tb2 B
ON B.s3 <= A.s3
GROUP BY A.s3;
-- select * from S1Percentiles;
-- select * from S2Percentiles;
-- select * from S3Percentiles;
UPDATE tb1 A
INNER JOIN
(
SELECT B.tb1_id AS id, (C.percentile + D.percentile + E.percentile) AS sum FROM tb2 B
INNER JOIN S1Percentiles C
ON B.s1 = C.s1
INNER JOIN S2Percentiles D
ON B.s2 = D.s2
INNER JOIN S3Percentiles E
ON B.s3 = E.s3
) F
ON A.id = F.id
SET A.sum = F.sum;
-- SELECT * FROM tb1;
DROP TABLE S1Percentiles;
DROP TABLE S2Percentiles;
DROP TABLE S3Percentiles;
它所做的是记录每个分数组的百分位数,然后最后用必要的数据更新tb1
列,而不是重新计算每个学生行的百分位数。
您还应该索引s1
, s2
和s3
列,以优化对这些列的查询。
注意:请根据您的数据库模式更新列名。还要注意,每个百分位数的计算都乘以100
,因为我相信百分位数通常是这样计算的。