检索男性和女性成员低选择性的最新更新时间？

huangapple
go评论109阅读模式
英文:

Retrieve latest update time for male and female members with low selectivity?

问题

以下是代码部分的翻译：

This is a SQL Server and want to find out the latest update time for male and female members. What is the most efficient way to create an index to find the data I need with such low selectivity? Thanks for all your help.

SELECT sex, COUNT(1) FROM membership

Produces:

sex COUNT(1)
M 887409
F 679003

And

SELECT sex, max(UpdateTime) FROM membership

Produces:

sex max(UpdateTime)
M 2023-05-29 18:30:09
F 2023-05-27 12:23:55
英文:

This is a SQL Server and want to find out the latest update time for male and female members. What is the most efficient way to create an index to find the data I need with such low selectivity? Thanks for all your help.

  1. SELECT sex, COUNT(1) FROM membership

Produces:

sex COUNT(1)
M 887409
F 679003

And

  1. SELECT sex, max(UpdateTime) FROM membership

Produces:

sex max(UpdateTime)
M 2023-05-29 18:30:09
F 2023-05-27 12:23:55

答案1

得分: 2

第一个查询可以纯粹通过在(sex)上创建的单列索引来支持。但是，两个查询可以通过以下索引来支持：

  1. INDEX IX NONCLUSTERED (sex ASC, UpdateTime DESC)
  2. -- 或者
  3. INDEX IX CLUSTERED (sex ASC, UpdateTime DESC)

在这种情况下，第二个查询可以通过为每个值分别执行TOP 1查询来进行改进：

  1. SELECT
  2.   m.*
  3. FROM (VALUES
  4.   ('M'),
  5.   ('F')
  6. ) v(sex)
  7. CROSS APPLY (
  8.     SELECT TOP (1)
  9.       m.sex,
  10.       m.UpdateTime
  11.     FROM membership m
  12.     WHERE m.sex = v.sex
  13.     ORDER BY UpdateTime DESC
  14. ) m;

如果您有一个列，不知道确切的值，但希望执行类似的跳跃扫描技术，您可以使用递归CTE和一个函数来实现：

  1. CREATE FUNCTION dbo.GetTopValue (@sexPrevious char(1))
  2. RETURNS TABLE
  3. AS RETURN
  4.     SELECT TOP (1)
  5.       sex,
  6.       UpdateTime
  7.     FROM membership
  8.     WHERE sex > @sex
  9.     ORDER BY UpdateTime DESC;

然后，使用以下代码来执行查询：

  1. WITH cte AS (
  2.     SELECT TOP (1)
  3.       sex,
  4.       UpdateTime
  5.     FROM membership
  6.     ORDER BY sex ASC, UpdateTime DESC
  8.     UNION ALL
  10.     SELECT
  11.       t.sex,
  12.       t.UpdateTime
  13.     FROM cte
  14.     CROSS APPLY dbo.GetTopValue(cte.sex) t
  15. )
  16. SELECT *
  17. FROM cte;

请注意，以上代码段是SQL查询和代码示例，无需翻译。

英文:

The first query can be supported purely by a single column index on (sex). But both queries can instead be supported by the following index

  1. INDEX IX NONCLUSTERED (sex ASC, UpdateTime DESC)
  2. -- alternatively
  3. INDEX IX CLUSTERED (sex ASC, UpdateTime DESC)

The second query could be improved in this case, by instead doing separate TOP 1 queries for each value

  1. SELECT
  2.   m.*
  3. FROM (VALUES
  4.   ('M'),
  5.   ('F')
  6. ) v(sex)
  7. CROSS APPLY (
  8.     SELECT TOP (1)
  9.       m.sex,
  10.       m.UpdateTime
  11.     FROM membership m
  12.     WHERE m.sex = v.sex
  13.     ORDER BY UpdateTime DESC
  14. ) m;

If you have a column where you don't know the exact values, but wish to do a similar skip-scanning technique, you can use a recursive CTE and a function instead.

  1. CREATE FUNCTION dbo.GetTopValue (@sexPrevious char(1))
  2. RETURNS TABLE
  3. AS RETURN
  4.     SELECT TOP (1)
  5.       sex,
  6.       UpdateTime
  7.     FROM membership
  8.     WHERE sex > @sex
  9.     ORDER BY UpdateTime DESC;
  1. WITH cte AS (
  2.     SELECT TOP (1)
  3.       sex,
  4.       UpdateTime
  5.     FROM membership
  6.     ORDER BY sex ASC, UpdateTime DESC
  8.     UNION ALL
  10.     SELECT
  11.       t.sex,
  12.       t.UpdateTime
  13.     FROM cte
  14.     CROSS APPLY dbo.GetTopValue(cte.sex) t
  15. )
  16. SELECT *
  17. FROM cte;

huangapple
  • 本文由 发表于 2023年6月2日 08:06:53
  • 转载请务必保留本文链接：https://go.coder-hub.com/76386423.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定