主页 / dba / 问题 / 281538
Accepted
artfulrobot
Asked: 2020-12-15 10:10:36 +0800 CST

如何在 MySQL/MariaDB 中使用 FIRST_VALUE 窗口函数来获取每组一行？

  • 772

我很高兴看到窗口函数登陆 MariaDB 10.2。我认为它们会很好地解决第一组问题，但我正在努力了解它们的效率。我有这样的事情：

CREATE TABLE email (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,  
                    contact_id INT UNSIGNED NOT NULL, /* FK */            
                    email VARCHAR(200),                                   
                    is_primary TINYINT(1) UNSIGNED NOT NULL DEFAULT 0)    
;                                                                         
                                                                          
INSERT INTO email (id, contact_id, email, is_primary)  VALUES                 
  (1, 1, '[email protected]', 0),                                         
  (2, 1, '[email protected]', 1),                                     
  (3, 1, '[email protected]', 0),
  (4, 2, '', 1),
  (5, 2, '[email protected]', 0),
;

我想要每个联系人的列表以及最适合他们的电子邮件。“最佳”被定义为：is_primary如果存在的话，更喜欢他们的。

我要这个：

Contact ID     Email ID             Email
-------------- -------------------- ------------------
1              2                    [email protected]
2              5                    [email protected]

使用窗口功能，我可以获得最好的电子邮件，例如

SELECT contact_id,
  FIRST_VALUE(email) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email,
  FIRST_VALUE(id) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email_id 
FROM email
WHERE email != ''
;                                                                                       

+------------+--------------------------+---------------+ 
| contact_id | best_email               | best_email_id | 
+------------+--------------------------+---------------+ 
|          1 | [email protected] |             2 |
|          1 | [email protected] |             2 |
|          1 | [email protected] |             2 | 
|          2 | [email protected]        |             5 | 
+------------+--------------------------+---------------+

但我注意到

  1. 使用n封有效（或至少非空）电子邮件，我得到n行输出。
  2. 我不得不复制逻辑：partition by contact_id对于每个SELECT; 这感觉效率低下：如果我在电子邮件表中有 12 个其他列需要其数据，我将运行 12 次，除非我只是在 ID 字段上执行此操作，然后在该最佳 ID 上再次进行 INNER JOINED 电子邮件。

所以为了得到我需要的东西，我最终是这样的：

SELECT contact_id, MIN(best_email) best_email, MIN(best_email_id) best_email_id
FROM (                                        
  SELECT  contact_id,                                                                 
    FIRST_VALUE(email) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email,
    FIRST_VALUE(id) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email_id 
  FROM email                                  
  WHERE email != ''                                                                        
) q                                                                                        
GROUP BY contact_id
;

但这感觉效率很低：MIN()需要检查每一行，即使它们都是一样的。我可以这样做：

SELECT contact_id, best_email, best_email_id                             
FROM (                                                                   
  SELECT contact_id, row_number() OVER (PARTITION BY contact_id) r,      
    FIRST_VALUE(email) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email,
    FIRST_VALUE(id) OVER (PARTITION BY contact_id ORDER BY is_primary DESC) best_email_id 
  FROM email                                                                              
  WHERE email != ''                                                                       
) q                                                                                       
WHERE q.r=1;

但它仍然感觉次优。

这似乎更有效：

SET @nth=0, @c=null;                                                          
SELECT id, email FROM (
  SELECT @nth := IF(@c = contact_id, @nth + 1, 1) r, id, email, @c:=contact_id dummy
   FROM email
  WHERE email != ''                                                              
  ORDER BY contact_id, is_primary DESC                                           
) sq
WHERE sq.r = 1;

我错过了什么吗？也许这不是窗口函数的正确位置？

mysql mariadb

1 个回答

  1. Best Answer

    我不确定我是否理解这个问题，但我会试一试。这将满足您的样本数据和预期结果：

    select contact_id, email_id as email_id, email 
from (
    select contact_id, id as email_id, email
     , row_number() over (partition by contact_id 
                          order by is_primary desc) as rn
    from email
    where email <> ''
) as t 
where rn = 1;

    由于您使用 row_number 枚举结果集，我不确定您为什么需要 first_value。

    正如您所指出的，FIRST_VALUE 不会以任何方式过滤结果集，它只是使用第一个值扩展每一行。

    • 1

相关问题