我正在尝试执行查询,以选择由另一个表中的启动和结束列定义的范围内的所有行。例如,使用伪代码,如果我有这些(非常小的(表:
ranges:
group_id = c("a", "b", "c", "d"),
start = c(1, 7, 2, 25),
end = c(5, 23, 7, 29)
positions:
position = 100 random numbers
annotation = 100 random strings
我想进行一个查询,该查询会返回以下内容:
group_id position annotation
a 2 adfkjdas
a 3 sdlfkjasl;kdfj
b 9 sdlfkdj
c 5 wwlekrj
d 27 zxcvzx
使用Mariadb/MySQL,在查询之间将在范围内进行行操作,因此这将有效:
SELECT
ranges.group_id as group_id,
positions.position as position,
positions.annotation as annotation
FROM
(SELECT * FROM my_ranges) AS ranges, positions
WHERE
positions.position BETWEEN ranges.start AND ranges.end
也就7或25至29之间(。
似乎操作员之间的行为在Presto中的行为不同,因此相同的查询不会返回任何结果。
Realty,我的范围表有约20,000个范围,我想查询约20,000范
这里有人可以建议一种修改此查询(或我的一般方法!(的方法吗?
(在评论中添加(:对于比伪代码更多的SQL,我想
use tables like this:
CREATE TABLE IF NOT EXISTS `ranges` (
`group_id` char,
`start` int(3),
`end` int(3)
);
INSERT INTO `ranges` (`group_id`, `start`, `end`) VALUES
('a', '2', '5'),
('b', '7', '23'),
('c', '2', '7'),
('d', '25', '29');
CREATE TABLE IF NOT EXISTS `positions` (
`position` int(3),
`annotation` varchar(20)
);
INSERT INTO `positions` (`position`, `annotation`) VALUES
('2', 'adfkjdas'),
('3', 'sdlfkjasl;kdfj'),
('5', 'wwlekrj'),
('9', 'sdlfkdj'),
('27', 'zxcvzx');
和这样的查询:
SELECT
group_id,
position,
annotation
FROM
ranges, positions
WHERE
positions.position BETWEEN ranges.start AND ranges.end
以下对我有用。我必须解决end
是一个保留词的事实:
CREATE EXTERNAL TABLE IF NOT EXISTS ranges (
group_id string,
start_value int,
end_value int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://my-bucket/ranges/';
CREATE EXTERNAL TABLE IF NOT EXISTS positions (
position int,
annotation string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://my-bucket/positions/';
SELECT
group_id,
position,
annotation
FROM
ranges, positions
WHERE
positions.position BETWEEN ranges.start_value AND ranges.end_value;
ranges
和positions
目录包含CSV文件:
a,2,5
b,7,23
c,2,7
d,25,29
和
2,adfkjdas
3,sdlfkjaslkdfj
5,wwlekrj
9,sdlfkdj
27,zxcvzx