SQL排名解决方案 [英] SQL ranking solution

问题描述

我正在为我的一个表实施排名解决方案，以优化读取查询，以摆脱使用 COUNT(*)、LIMIT 和 OFFSET 子句的昂贵查询.我的问题是我不知道为什么位置计算不正确.请查看我的示例以重现问题.

I am implementing ranking solution for one of my tables to optimize read queries to get rid of expensive queries which use COUNT(*), LIMIT and OFFSET clause. My problem is that I don't know why position calculation are incorrect. Please look at my example to reproduce problem.

CREATE TABLE `acl`
(
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `limiter` INTEGER(11) SIGNED NULL,
    PRIMARY KEY (`id`)
)
ENGINE=INNODB;

CREATE TABLE `quote`
(
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `created_at` INTEGER(11) UNSIGNED NOT NULL,
    `reputation` INTEGER(11) SIGNED NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=INNODB;

INSERT INTO `acl` (`name`, `limiter`) VALUES ('Users', 0), ('Staff', null);
INSERT INTO `quote` (`created_at`, `reputation`) 
  VALUES (UNIX_TIMESTAMP(), 0), (UNIX_TIMESTAMP()+1, 0);

SET @acl_id := 0, @position := 0;
SELECT acl.id AS acl_id, quote.id AS quote_id, 
  GREATEST(@position := IF(@acl_id = acl.id, @position + 1, 1), 
    LEAST(0, @acl_id := acl.id)) AS position 
FROM acl JOIN quote 
  ON (acl.limiter IS NULL OR quote.reputation >= acl.limiter) 
ORDER BY acl.id ASC, quote.created_at DESC;

我希望选择查询获取所有 acl 行并同时将它们与引用行连接起来，设置它们的位置，但我得到的只是每一行的 position=1.有人建议我将变量赋值移动到 JOIN 或 ORDER 子句，但问题仍然存在.我的问题是...如何在单个查询中分配位置?

I would like that select query to fetch all acl rows and join them with quote rows at the same time, set their position, but all I get is position=1 for every row. Someone suggested me to move variable assignments to JOIN or ORDER clause but the problem remains. My question is... how to assign position in single query?

推荐答案

我博客文章中的一个稍微修改的查询:

A slightly modified query from the article in my blog:

• 分析函数:SUM、AVG、ROW_NUMBER

 

SELECT  q.*,
        @r := @r + 1
FROM    (
        SELECT  @_acl_id := -1,
                @r := 0
        ) vars
STRAIGHT_JOIN
        (
        SELECT  acl.id AS acl_id, quote.id AS quote_id
        FROM    acl
        JOIN    quote
        ON      (acl.limiter IS NULL OR quote.reputation >= acl.limiter)
        ORDER BY
                acl.id ASC, quote.created_at DESC
        ) q
WHERE   CASE WHEN @_acl_id <> acl_id THEN @r := 0 ELSE 0 END IS NOT NULL
        AND (@_acl_id := acl_id) IS NOT NULL

这篇关于SQL排名解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！