如何从数据库中逐个返回样本一行 [英] How to return sample row from database one by one

问题描述

网页应该显示从PostgreSQL数据库特定的产品类别一个产品的形象。
此图像应每25秒后自动变更为其他图像。
返回的产品可以是无规或在某些序列。有些产品可能会丢失一些反复，但大部分产品的标准应该返回。
总的可用图像计数可能取回样品之间稍微改变

目前低于code用于这是每25秒后执行。
这需要两个查询数据库：一个用于计数可slwo和第二的
单一的图像检索。在where子句都是相同的，在实际应用中这两种情况下where子句是非常大的，改变它需要在两个地方的变化。

如何提高这使单个查询返回的样品？
列类型不能改变，使用天然的主键。其他列，触发器，索引，序列可以增加，如果这有助于。

ASP.NET/Mono MVC3，npgsql被使用。

  $计数= SELECT COUNT（*）
         从产品
         其中，prodtype = $ sometype。这时候和在产品ID（选择图像的productid）;0之间$随机=下一个随机整数.. $计数1; -  $ productsample是结果是：所需的采样产品
$ productsample =选择产品
          从产品
          其中，prodtype = $ sometype。这时候和在产品ID（选择图像的productid）
          偏移$随机
          限制1;
创建表产品（产品ID CHAR（20）主键，
   prodtype CHAR（10）引用producttype
）;创建表的图像（
ID的序列主键，
焦炭产品ID（20）引用的产品，
mainimage布尔
）;
 

解决方案

这是按为了将永远是昂贵特别是如果在订单中前pression由是未编入索引。所以，不要命令。在做，而不是一个随机的计数偏移（）为您的查询，但这样做的一次。

 伴t为（
    选择 *
    从
        商品P.
        内部联接
        我使用的图像（产品ID）
    哪里
        prodtype = $ sometype。这时候
）
选择 *
从T
偏移地板（随机（）*（从T SELECT COUNT（*）））
限制1
 

此版本可能会更快

 伴t为（
    SELECT *，COUNT（*）OVER（）总
    从
        商品P.
        内部联接
        我使用的图像（产品ID）
    哪里
        prodtype = $ sometype。这时候
）
选择 *
从T
偏移地板（随机（）*（选择从T限1个））
限制1
 

Web page should show one product image for specific product category from PostgreSql database. This image should changed automatically to other image after every 25 seconds. Returned product may be random or in some sequence. Some product may be missing and some repeated but most of the products in criteria should returned. Total available image count may change slightly between sample retrieval

Currently code below is used which is executed after every 25 seconds. This requires two queries to database: one for count which may be slwo and second for single image retrieval. In both cases where clauses are duplicated, in real application where clause is very big and changing it requires changes in two places.

How to improve this so that single query returns sample ? Column types cannot changed, natural primary keys are used. Additional columns, triggers, indexes, sequences can added if this helps.

ASP.NET/Mono MVC3 , npgsql are used.

$count = select count(*)
         from products
         where prodtype=$sometype and productid in (select productid from images);

$random = next random integer between 0 .. $count-1;

--  $productsample  is result: desired sample product
$productsample = select product
          from products
          where prodtype=$sometype and productid in (select productid from images)
          offset $random
          limit 1;


create table products ( productid char(20) primary key,
   prodtype char(10) references producttype 
);

create table images(
id serial primary key, 
productid char(20) references products,
mainimage bool
);

解决方案

An order by will always be expensive specially if the expression in the order by is not indexed. So don't order. In instead do a random offset in the count() as in your queries, but do it all at once.

with t as (
    select *
    from
        products p
        inner join
        images i using (productid)
    where
        prodtype = $sometype
)
select *
from t
offset floor(random() * (select count(*) from t))
limit 1

This version might be faster

with t as (
    select *, count(*) over() total
    from
        products p
        inner join
        images i using (productid)
    where
        prodtype = $sometype
)
select *
from t
offset floor(random() * (select total from t limit 1))
limit 1

这篇关于如何从数据库中逐个返回样本一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！