代码之家 › 专栏 › 技术社区 › hhaamu

SQL按“循环”顺序返回行

postgresql sql

hhaamu · 技术社区 · 16 年前

我有一堆URL存储在一个表中,等待脚本擦除。但是,这些URL中的许多都来自同一个站点。我想以“站点友好”的顺序返回这些URL(也就是说,尽量避免连续两个URL来自同一个站点),这样我就不会在短时间内被过多的HTTP请求意外阻止。

数据库布局如下:

create table urls (
    site varchar,       -- holds e.g. www.example.com or stockoverflow.com
    url varchar unique
);

实例结果:

SELECT url FROM urls ORDER BY mysterious_round_robin_function(site);

http://www.example.com/some/file
http://stackoverflow.com/questions/ask
http://use.perl.org/
http://www.example.com/some/other/file
http://stackoverflow.com/tags

我想到了类似的 ORDER BY site <> @last_site DESC “但是我不知道怎么写这样的东西。

5 回复 | 直到 16 年前

Quassnoi 16 年前

有关如何工作的详细说明,请参阅我的博客中的这篇文章:

PostgreSQL: round-robin order

用新 PostgreSQL 8.4 :

SELECT  *
FROM    (
        SELECT  site, url, ROW_NUMBER() OVER (PARTITION BY site ORDER BY url) AS rn
        FROM    urls
        )
ORDER BY
        rn, site

对于旧版本:

SELECT  site,
        (
        SELECT  url
        FROM    urls ui
        WHERE   ui.site = sites.site
        ORDER BY
                url
        OFFSET  total
        LIMIT   1
        ) AS url
FROM    ( 
        SELECT  site, generate_series(0, cnt - 1) AS total
        FROM    (
                SELECT  site, COUNT(*) AS cnt
                FROM    urls
                GROUP BY
                        site
                ) s
        ) sites
ORDER BY
        total, site

尽管效率会降低。

Keith Adler 16 年前

我觉得你做得太复杂了。为什么不只用

按newid()排序

Wojciech Bederski 16 年前

你要的是循环赛,但我认为

SELECT site, url FROM urls ORDER BY RANDOM()

会成功的。即使来自同一站点的URL聚集在数据库中,它也应该工作。

Community CDub 8 年前

如果URL不经常更改,您可以想出一个稍微复杂的作业,您可以定期(每夜)运行它。它将根据存在的不同站点为每个记录分配整数。

您所能做的就是编写一个从URL解析域的例程(您应该能够在任何地方找到这样做的代码片段)。

然后,创建一个临时表,其中包含每个唯一域和一个数字。

然后,对于URL表中的每个记录,您可以在临时表中查找域,将存储在其中的记录编号分配给该记录,并向临时表编号中添加一个大的数字。

接下来的一天,按数字排序。

下面是您在问题中使用的五个记录的示例:

网址:

临时表:

example.com       1
stackoverflow.com 2
perl.org          3

然后,对于每个URL,在temp表中查找值,并向其添加3个(因为它有3个不同的记录):

迭代1:

网址:

http://www.example.com/some/file         1
http://www.example.com/some/other/file   NULL
https://stackoverflow.com/questions/ask   NULL
https://stackoverflow.com/tags            NULL
http://use.perl.org/                     NULL

临时表:

example.com       4
stackoverflow.com 2
perl.org          3

迭代2:

网址:

http://www.example.com/some/file         1
http://www.example.com/some/other/file   4
https://stackoverflow.com/questions/ask   NULL
https://stackoverflow.com/tags            NULL
http://use.perl.org/                     NULL

临时表:

example.com       7
stackoverflow.com 2
perl.org          3

等等,直到你

http://www.example.com/some/file         1
http://www.example.com/some/other/file   4
https://stackoverflow.com/questions/ask   2
https://stackoverflow.com/tags            5
http://use.perl.org/                     3

很多记录都会很慢。对于许多插入/删除操作来说,这是很困难的,但结果将是一个完美的循环排序。

bobflux 16 年前

有一个简单快捷的解决方案…

添加文本类型的排序列
添加一个on-insert触发器,该触发器将排序顺序设置为md5(url)
按排序顺序索引
按(排序、主键)顺序抓取行

->它非常快速并且索引 ->行将以可重复但随机的顺序出现