编辑更新:原来我有版本5.7,所以窗口功能不是一个选项,以找到一个解决方案。
SHOW VARIABLES LIKE 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.7.21-log |
+---------------+------------+
问题描述:我有一个报价、技能和简介之间的三元关系表。这种三元关系有一个属性,排名。
我有一个技能表,我可以看到技能的名称。到现在为止,我不得不做两个问题:
1) 请给出每个档案中排名前10位的技能:
SELECT DISTINCT ternary.id_skill, skill.name_skill, ranking_skill
FROM ternary
INNER JOIN skill ON skill.id_skill=ternary.id_skill
WHERE ternary.id_perfil= #IntNumber#
GROUP BY ternary.id_skill
ORDER BY ternary.ranking_skill DESC
LIMIT 10;
2) 关于身份技能列表,请告诉我它们是否出现在任何个人资料中,以及它们出现的次数。
SELECT DISTINCT ternary.id_profile, nombre_profile, COUNT(DISTINCT ternary.id_skill) AS matching
FROM ternary
INNER JOIN profile ON ternary.id_profile=profile.id_profile
WHERE ternary.id_skill= '858534430'
OR ternary.id_skill= '3213227'
OR ternary.id_skill= '3254818'
GROUP BY(ternary.id_profile)
ORDER BY matching DESC;
在最后一个查询中,发现了一个问题:它“搜索”某个技能出现在某个配置文件的任意点上。既然一个个人资料有可能拥有数千种技能,那么这可能会产生误导,因为我们想要达到的目标,我现在只需要“搜索”任何个人资料的前十大技能之一。但只有前10名。
到目前为止,基本上我一直在尝试混合这两个查询,但收效甚微,因为我似乎无法在两个列上进行分区,即使我只使用一个,也会得到一个 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY
:
SELECT *
FROM
(
SELECT DISTINCT ternary.id_skill,
skill.name_skill,
ternary.ranking_skill,
ternary.id_profile,
ROW_NUMBER() OVER(PARTITION BY id_profile, id_skill ORDER BY ternary.ranking_skill DESC) rn
FROM ternary
INNER JOIN skill ON skill.id_skill=ternary.id_skill
)
WHERE rn < 11;
我知道这个操作可能被称为groupwise maximum,我已经看到了一些寻找这个的答案。我还不能复制它们中的任何一个,我需要它专门用于 mysql Ver 14.14 Distrib 5.5.60, for Linux (x86_64) using readline 5.
如果有什么帮助的话(我尝试过一些非常适合其他类似数据库的答案,但在mysql中不起作用)。
表定义:
CREATE TABLE `ternary` (
`id_offer` varchar(200) NOT NULL,
`id_skill` varchar(200) NOT NULL,
`id_profile` varchar(200) NOT NULL,
`ranking_skill` double NOT NULL,
PRIMARY KEY (`id_offer`,`id_skill`,`id_profile`),
KEY `id_skill` (`id_skill`),
KEY `id_profile` (`id_profile`),
CONSTRAINT `ternary_ibfk_1` FOREIGN KEY (`id_offer`) REFERENCES `offer` (`id_offer`),
CONSTRAINT `ternary_ibfk_2` FOREIGN KEY (`id_skill`) REFERENCES `skill` (`id_skill`),
CONSTRAINT `ternary_ibfk_3` FOREIGN KEY (`id_profile`) REFERENCES `profile` (`id_profile`)
)
CREATE TABLE `skill` (
`id_skill` varchar(200) NOT NULL,
`name_skill` varchar(200) DEFAULT NULL,
`date` date DEFAULT NULL,
PRIMARY KEY (`id_skill`)
)
做实验的结果
select * from ternay limit 10;
+------------+------------+-----------+----------------------+
| id_oferta | id_skill | id_perfil | ranking_skill |
+------------+------------+-----------+----------------------+
| 1004 | 107 | 679681082 | 0 |
| 1004 | 115 | 679681082 | 0.10846866454897801 |
| 1004 | 117 | 679681082 | 0.038003619695992294 |
| 1004 | 129 | 679681082 | 0.04987975085098989 |
| 1004 | 147 | 679681082 | 0.02771097269499438 |
| 1004 | 299 | 679681082 | 0.0522549770819894 |
| 1004 | 321 | 679681082 | 0.11955305362697576 |
| 1004 | 417 | 679681082 | 0.11321911701097703 |
| 1004 | 964 | 679681082 | 0.015043099462996949 |
| 1004 | 967 | 679681082 | 0.05304671915898924 |
+------------+------------+-----------+----------------------+
查询结果1)描述上述,这给了我一个配置文件前10名
+------------+--------------+---------------------+
| id_skill | name_skill | ranking_skill |
+------------+--------------+---------------------+
| 109 | scala | 0.3089840175329823 |
| 122 | hadoop | 0.24164146109602963 |
| 9731 | python | 0.21470443852124863 |
| 325 | java | 0.18776741594646754 |
| 114 | sql | 0.14736188208429596 |
| 101 | kafka | 0.13389337079690544 |
| 301 | bbdd | 0.13389337079690544 |
| 927 | agile | 0.13389337079690544 |
| 320 | hive | 0.1204248595095149 |
| 109 | spark | 0.1204248595095149 |
+------------+--------------+---------------------+
2条答案
按热度按时间1aaf6o9v1#
这是你做的样品
Row_number()
使用outwindow函数,您可以尝试在select
条款。PARTITION BY
子查询where子句中的列写入条件。子查询
count(*)
使Row_number
像这样。sqlfiddle:http://sqlfiddle.com/#!9月7日EE529/9
此查询可能是您可以尝试的工作。
kdfy810k2#
要加快第一次查询的速度,请更改
到
别混了
DISTINCT
以及GROUP BY
. (groupby有效地做到了与众不同。)你在哪里
nombre_profile
从哪里来(当存在悬而未决的列名时,很难提供帮助。)延迟获取
skill.name_skill
.别费心路过
ranking_skill
如果不使用,则从子查询中删除。移动其中一个
JOIN
进入子查询。也许这有正确组合两个查询的效果: