使用具有非唯一值的条件进行 SQL 重复数据删除-解网

问：

我有一个名为 cc_index 的表，该表包含以下列：length、date、url、[other_colums]。

同一 URL 可能会出现在多行中。对于每个 url，我只想保留一行（但所有列）。我想保留长度最长的一行，不幸的是，这一行可能不是唯一的。如果同一 url 的多行长度值最高，我想保留具有最新（最高）日期的行，日期值对于每行都是唯一的。

为了澄清这一点，这是一个可能的输入表示例：

网址	长度	日期	其他
abc.com	42	292	阿兹克
abc.com	36	312	SDLKF公司
abc.com	42	281	SDJL公司
def.com	12	210	汤姆
def.com	18	112	VKR的
ghi.com	29	292	汤姆
ghi.com	29	103	泰尔
ghi.com	29	101	VNV

我想要的输出如下：

网址	长度	日期	其他
abc.com	42	292	阿兹克
def.com	18	112	VKR的
ghi.com	29	292	汤姆

我知道我可以运行这样的查询进行重复数据删除

SELECT t1.*
FROM ccindex t1
INNER JOIN (SELECT url, MAX(length) AS max_length FROM ccindex GROUP BY url) t2
ON t1.url=t2.url AND t1.length = t2.max_length

但是，如果条件不是唯一的（长度），这将返回每个 URL 具有最大长度的所有行，因此同一 URL 的多行，即输出将是

不需要的输出：

网址	长度	日期	其他
abc.com	42	292	阿兹克
abc.com	42	281	SDJL公司
def.com	18	112	VKR的
ghi.com	29	292	汤姆
ghi.com	29	103	泰尔
ghi.com	29	101	VNV

如何修改此查询以仅为length=max_length的每个网址选择一行？

跟进：如果我不关心日期，并且想要返回具有最大长度的单个任意行，是否可以显着提高性能？

sql mysql 重复 greatest-n-per-group mysql-5.5

SELECT t1.*
FROM ccindex t1
WHERE (t1.url, t1.length, t1.date) IN (
    SELECT max_lengths.url, max_lengths.max_length, max_date
    FROM (
        SELECT url, MAX(length) AS max_length
        FROM ccindex
        GROUP BY url
    ) AS max_lengths
    JOIN (
        SELECT url, length, MAX(date) AS max_date
        FROM ccindex
        GROUP BY url, length
    ) AS max_dates
    ON max_lengths.url = max_dates.url AND max_lengths.max_length = max_dates.length
);

0赞 Swifty 9/29/2023 #2

我只想用它来：ROW_NUMBER() OVER (PARTITION...)

WITH t1 AS (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY url ORDER BY length DESC, date DESC) AS n_row
    FROM ccindex
    )
SELECT *
FROM t1
WHERE n_row = 1

在第二个 SELECT 中，您可能希望仅将 * 替换为所需的行。

0赞 Jonas Metzler 9/29/2023 #3

我们可以在这里使用。DENSE_RANK

该部分构建了每个，该子句确保以 highest 开头，如果与 latest 相同：PARTITION BYurlORDER BYlengthdate

WITH rankedData AS
(SELECT
  url, length, date, other,
  DENSE_RANK() OVER(PARTITION BY url ORDER BY length DESC, date DESC) AS ranking
FROM ccindex)
SELECT
  url, length, date, other
FROM rankedData 
WHERE ranking = 1
ORDER BY url;

如果要显示子查询和主查询，请向它们添加更多列。

对于你的问题...

如果我不关心日期，并且想要返回具有最大长度的单个任意行，是否可以显着提高性能？

...我不认为检查最新日期会对您的表现产生太大影响。但是我们不知道你的表有多大，它有什么索引等。因此，我建议简单地尝试这两个查询并比较执行时间。

关于性能，更重要的当然是使用最新版本的RDBMS。所以这个答案假设（正如您在上一条评论中提到的）您已经在使用它，或者您将升级。

尝试在此示例中对上述查询来摆弄您的数据。

查看有关以下内容的文档DENSE_RANK

上一个：在滑动窗口框架中查找最大列值和最小列值

下一个：返回计数和平均值的有效方法，无需将多个 CTE 连接在一起

使用具有非唯一值的条件进行 SQL 重复数据删除

SQL deduplication with criteria that has non-unique values

评论