选择下一个位置

发布于 2024-11-05 08:55:27 字数 268 浏览 1 评论 0原文

是否可以将这个查询（来自 Postgres）表达为 HQL 或 Hibernate 标准查询？

SELECT id, least(abs(gene_start - ?) , abs(gene_end - ?)) as div_pos
FROM arraydata.gene WHERE chromosomeref = ?
ORDER BY div_s LIMIT 1

或者是否有其他方法来选择给定位置旁边的条目。表中一个条目的开头或结尾与查询的距离最小。

原文

Is it possible to express this query (from Postgres) as in HQL or as a hibernate criteria query?

SELECT id, least(abs(gene_start - ?) , abs(gene_end - ?)) as div_pos
FROM arraydata.gene WHERE chromosomeref = ?
ORDER BY div_s LIMIT 1

Or is there a other way to select a entry there is next to a given position. Where the start or the end of one entry from the table has the smallest distance to the query.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

离线来电— 2024-11-12 08:55:27

这个东西：

least(abs(gene_start - ?) , abs(gene_end - ?))

根本不可索引。我想你有gene_start和gene_end的索引，所以你可以这样做：

SELECT id, gene_start
FROM arraydata.gene WHERE chromosomeref = ?
WHERE gene_start > ?
ORDER BY gene_start LIMIT 1

SELECT id, gene_end
FROM arraydata.gene WHERE chromosomeref = ?
WHERE gene_end < ?
ORDER BY gene_end DESC LIMIT 1

你可以将两者与UNION结合起来。至于在 Hibernate 中表达它，不知道！

This stuff :

least(abs(gene_start - ?) , abs(gene_end - ?))

is not indexable at all. I suppose you got indexes on gene_start and gene_end, so you could do :

SELECT id, gene_start
FROM arraydata.gene WHERE chromosomeref = ?
WHERE gene_start > ?
ORDER BY gene_start LIMIT 1

SELECT id, gene_end
FROM arraydata.gene WHERE chromosomeref = ?
WHERE gene_end < ?
ORDER BY gene_end DESC LIMIT 1

You can combine both with UNION. As for expressing it in Hibernate, no idea !

回复收藏 0 原文

~没有更多了~