带有PARTITION BY子句的ROW_NUMBER()在MariaDB上停止工作

q3aa0525  于 2022-11-08  发布在  其他
关注(0)|答案(2)|浏览(153)

我已经在我的生产服务器上使用一个带有这个SQL查询的应用程序超过3年了，没有对查询或所使用的MariaDB服务器的主要版本进行任何修改。它突然停止工作，我不知道为什么。
查询：

SELECT 
  c.* 
FROM 
  contestants c 
WHERE 
  (
    date BETWEEN '2022-08-01 00:00:00' 
    AND '2022-08-31 23:59:59'
  ) 
  AND (winner = 0) 
ORDER BY 
  ROW_NUMBER() OVER (
    PARTITION BY email 
    ORDER BY 
      RAND()
  ) 
LIMIT 
  10;

**预期行为：**从contestants表（该表包含提交的竞赛代码，每个参赛者在此表中可以有多行）中随机返回10个获胜者，每个email只能出现一次，只能从没有任何获胜代码的参赛者中选择（winner = 1）。同一查询会执行多次（多个奖项轮），此查询确保每个电子邮件每个月只能赢得一个奖项。这在过去3年多的时间里一直很有效。
**当前行为：**该查询似乎只是通过升序返回10个参赛者（ID 1、2、3 ...）。

如果我在不同的Web服务器上运行查询（本地或我的托管服务提供商之一），它运行得很好。我试过MariaDB 10.3、10.5和MySQL 8.0，它们都能正常工作。
我的生产服务器运行的是MariaDB 10.3.34，这似乎是2022年2月的安全更新。但是，我尝试在运行MariaDB 10.9.2的同一台服务器上创建另一个数据库，结果是相同的。
为什么这个查询突然停止工作，而在不同的环境中却工作得很好？
编辑：下面是一些可以尝试的示例数据：

CREATE TABLE `contestants` (
  `EntryID` int(11) DEFAULT NULL,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `psc` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `winner` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `contestants` (`EntryID`, `firstName`, `lastName`, `email`, `address`, `phone`, `code`, `city`, `psc`, `date`, `winner`) VALUES
(1, 'TEZvbGgxVW9oQkphNmdWaGpOdXJGZz09', 'OTRmVzZ3NjVkUUJIVzlZRUw4a0RzQT09', 'QTJlSHNwcWg2TEkrSFNpc05BcFBFcTh5YWFqcHZPekkrWCtzOWw1TWVtRT0=', 'T2J4cFl2eEwxS1R3YTJkcHpjbkx3Zz09', 'ZUI0U3o5djFXbHkrZk5KRGVXK25FUT09', 'WlJBSTl1VnFyRGk0UlBsSFRCT0pLQT09', 'Q094aGRURm1VODRsS0FrK2RYdGxNZz09', 'ZTJoaituNkJtZ0Yvc2EzamRqZmhJUT09', '2022-08-18 00:00:00', 0),
(2, 'aGZZK0dvbSs1UTIxN3VGcGxKdEZuZz09', 'VFE2VHRGaktnV0l1b21uemlUbytUUT09', 'dEIvbTI5OEhHZG5IdSsxMjVKUXpKZVlkYkVkcy9PQU00RnhsRUx0RnZIdz0=', 'KzgxQkVOb0xSNDkzRGFXcnhpM3Rtdz09', 'ZmJDMzE0V3UzaWxEcE9veXNtUFpZZz09', 'RlY3a01MSktZRlptYnhwdVJqamI1UT09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'ZWozRXJ5VUk2bUppU2NuMmZoVm9CZz09', '2022-08-19 00:00:00', 0),
(3, 'OERRNUVCank2aHBRRVdkVjJPZmNsUT09', 'OC80TStVdlFwTi9sbGFGQVNHcEZTZz09', 'SW9nVVZ2SCtnQUg2TzRYMDBsNTBFeStReHdDZTZKUHNocStTU3FPMG1mZz0=', 'Y0ZNdXNQdGRIVEJKNzlLTDJPV3l2dz09', 'N1cyUHR0S3FPbUdUbGFmenh4ZHhFdz09', 'SHN5MjRFWTR2Zmx5R1QzVEsyMVRBUT09', 'ZjVVN3ZSYnZNdmY2cHlqR080c1JzUT09', 'OFpPcEM5R1NJTHh2eFh1N0tMQzNGdz09', '2022-08-19 00:00:00', 0),
(4, 'WFFKYTVQR3lRZFA3eDdvR21KbGZaUT09', 'ZjVhTi95RW5kang1Q0JqU3dENkhjQT09', 'ZEhJZFlwNlJrSERneW5vMytkSU9qUmdtc1p2YlAxQWhjc2hxNzFIT0JBWT0=', 'UUtlVy9VMzlHVytpWnYvQTNLU1d0QT09', 'M0h5Mm5GNUw2cHUwbFV2K1Z4MWw3Zz09', 'WURMTjZNOWxpcm9JOFZkMWJQQUR4UT09', 'M0xxRkhpeEthTnZPWitzN3BxbTdrZz09', 'Q0dVMGE1VEkzRmxCSnN4ak5LYit6Zz09', '2022-08-22 00:00:00', 0),
(5, 'VVhBZGNXaUQzdkFpd1F2WVBSR3RQQT09', 'TnFJdHRURkRoUkYzMzlWeStva1hqZz09', 'WE84ZDRkNkNvNkRaZ1M3VGQ0MzVxV3FQRlFiNWNCWkZPZFA3WjJ6cWRHOD0=', 'NjRuMlhiVDBWejJjOTlRT1A1dmlTZz09', 'ZHRibW9VK09sZVpDTXlKTDVrRkhGZz09', 'akpwaWlLaVZIOXlvRFNJTHIxVzFTZz09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'bzJxbGJ5cU5mbXlVekNYdDdQTVc4UT09', '2022-08-22 00:00:00', 0),
(6, 'MDRlV0RZKzd2MkdEcnI5QWJjWXdHZz09', 'YWpKYTBqYmxieHdMaXEzd0VvYzJ2QT09', 'TGFLZ0lLMFBkKzZSSmxXd0FGOHFJR2xiYWZxdEJjMlBLZlJUUnNFbzVtRT0=', 'RFlkUkhMT0k5REJsang3V3Fyem9kZz09', 'ZytScDc5V2VyVnJsT0FmaWhkWFphZz09', 'UFJXVzBISXc1QVFHSm1WaC9aMjRZdz09', 'RFlkUkhMT0k5REJsang3V3Fyem9kZz09', 'a3hqcFV4NWVFNXZ3TU1rNWcvblNnUT09', '2022-08-22 00:00:00', 0),
(7, 'QzY4dzA5RDNERmNrTUk1R0pad1RCdz09', 'eWQ3aVBYUi9wcndNbDBQdk42a1l5UT09', 'LzhvcHFQMzh4NVV6a3FqQmZqbXk3OGpJa0FzS1N4a29rYmQrUER0Ny9Ybz0=', 'U2J2RWl4cGVOU3lkMkl0dnFrZjZUUT09', 'OWt1RHJ1RFZIUlltRUZPb2ZTUEhWdz09', 'bU9lUjd2SXJ5ZitKY2JmUFVsVjE5QT09', 'WHg4dmtyclF1OGtBL2NhRW9SalpIRDY2UmY3c0ptVUJvQm1SSWZHNWlTOD0=', 'RVR0SjVqNXhwam42UkZXajhITFNaZz09', '2022-08-23 00:00:00', 0),
(8, 'eEw4cWlhc09NVDMrRUp0WmpmdjBTQT09', 'YkYxRENEU3N2NEh6aDJoRjJwcUxFZz09', 'b3g2alU3ZDhPczFrdjRrMnRpSXZYZ1dRWXZLRFdEdXYvQ2VRZ2RDREsrST0=', 'RVZVQXNTd3ZsL2V3ajVQamdUaXFaZz09', 'ZGdIdGtYUjh3cmIvRnQ3V29mdy9lZz09', 'dHEzU3BualdSUTFySTFXVzloSFZiUT09', 'RnJreUhTekQ0YzZtWjVDUEc3R0Z6dz09', 'TUxOaUZvOFFQcGF2RTUvTmtQRjFDZz09', '2022-08-23 00:00:00', 0),
(9, 'REZXWXQ0UWxTV3k0N2NkdXJYZFI5Zz09', 'dUZKTGFiVG5Kb2FMTFBuTzlJUzRFZz09', 'QjJvbzhqVm9EZFQ5ZnlWVStDVjRKejh0T2FDRWpYdXdBZzUyQkQ0OUF1ND0=', 'UWQ3eDNsL2VwVFNRUTQvL05qQ3RyUT09', 'NU1Zb21QeVR1bmdrRXQ5aHBLRGtLZz09', 'SXZzQmRrRDFjbzYvaUMwWnRtTkgvQT09', 'M0xxRkhpeEthTnZPWitzN3BxbTdrZz09', 'bzJxbGJ5cU5mbXlVekNYdDdQTVc4UT09', '2022-08-23 00:00:00', 0),
(10, 'RFoxSXpUUEswRFZFUHdkaVZtSGtPZz09', 'VUxqd1o2ZnB5LzFqSUtiUUoyanFtZz09', 'b1VtQVgyZ0VOUnI0MWlEc1JqTmZZNjA0ZnlZcWJWU24wL0V6dWxyYTg5cz0=', 'WnErRS9MNGs3bkEwcnJxOThmVllCQT09', 'NU14VXRMelpYU3J2QThvUzNzLzBRdz09', 'cWMzM0dkY3NzR2sxNlVqVGFhRmQydz09', 'b1dkRStMcmg5RlVINWt6ZXprbmF3K0FyODlpVnFhSUZtaXJldEVaUnpjTT0=', 'ZWozRXJ5VUk2bUppU2NuMmZoVm9CZz09', '2022-08-23 00:00:00', 0),
(11, 'WG5LRVV6eTlmSU1Ha0JRRGllYWpkZz09', 'cC83K3B4ejVQSjB6bmVaWnppL1F0UT09', 'WmVhZHM4NnQvZjdNRnM5TElEcHhZRUF3MmxPalhVM1VIZFU3UldrMzdndz0=', 'Ymg5eHZ0dlhMYzlLVXdJV3N1YndFZz09', 'WTBIdnVTQ25JRlFRT2Rid1BCeVMxQT09', 'OUROelVUbHZZU0MxdGdsZ1NyUldaUT09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'bmVHNlc0bGFkREphVmxhNHpNMlBvdz09', '2022-08-24 00:00:00', 0),
(12, 'MnNNdjdKbm5qZDhuQzlEQWR3Y3FGQT09', 'RHl2ZFZYOUlsYWs1ZGVtU0JpSEtUQT09', 'T0hWdktudmw4Q2Z1dkFzaFZQOXhPeUJRdm0yMHNpMUJKakVIUmg0VmJpTT0=', 'dWZ0QnVwTm5ieFc2dVBFSEdzUmZ2WkxkTmZWdUZWSFkxWHV3bVBuNUdlQT0=', 'U2gxYk1CZVh6RlQ1QXFtandkblU5dz09', 'MGloTXdVQ2RPNVE4b00weWhIRU5RZz09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'cnJyRmk2dTlWZzRVYVg0NzVNUXVDQT09', '2022-08-24 00:00:00', 0),
(13, 'MnNNdjdKbm5qZDhuQzlEQWR3Y3FGQT09', 'RHl2ZFZYOUlsYWs1ZGVtU0JpSEtUQT09', 'T0hWdktudmw4Q2Z1dkFzaFZQOXhPeUJRdm0yMHNpMUJKakVIUmg0VmJpTT0=', 'dWZ0QnVwTm5ieFc2dVBFSEdzUmZ2WkxkTmZWdUZWSFkxWHV3bVBuNUdlQT0=', 'U2gxYk1CZVh6RlQ1QXFtandkblU5dz09', 'TmhXdDgyYnl1VVA1YTdSNUV3MVJNQT09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'cnJyRmk2dTlWZzRVYVg0NzVNUXVDQT09', '2022-08-24 00:00:00', 0),
(14, 'SXUwa0xDcUJZWnFhUmkreVpwRU9WZz09', 'U0RCS3B0ajJzYVVpczFHRWxhS2I3dz09', 'cVkxRlhYeE04NDgyRFJXWHlPTVFmVndxZW1sNmg5bWNleEhTSzE2bUk2TT0=', 'T3ZiM2JZaURzdnEwby8rWHhCT1Nldz09', 'eStPaWtERWJmTlV4WGNqTzNRMDlPQT09', 'a2QrazdHL2NQU1d6YWcwMTJ6c0JRZz09', 'Z1VXS05LdEdSY0d0bk1HQWg1NHBjdz09', 'TGhOdHowYmhhWEdYSE5ua1RuZUg0QT09', '2022-08-25 00:00:00', 0),
(15, 'L0xyVjZBNVY1OHZ6bFhyZXY3dkFBdz09', 'Z3JSUlpYRzM5VTN4L2ZZUG9pOHhlUT09', 'bUR2L2NlYWNGS1V6cWFuSjVyOEFtd2tGb1pRWmRmaG1TbGllaU1DbnEzRT0=', 'YUcvbjk1NXNLREJwV0YyMWFLQTN5ZFhlcHJqd3FQMXpzYmd4T0xGZmd0WT0=', 'VGdva2NUclQrdHMvOUdEcDRXeEVtUT09', 'MlFVaDRQc0hZV3ZkZUJHY0FpRkpTZz09', 'RzV5MEpHUmtKNlFtZWErQ3U4cFNpdz09', 'YlRiR1h3b01ma3BVRHVURXdwSUd0dz09', '2022-08-25 00:00:00', 0),
(16, 'WmJTSVk0WUlsTCtpeWV3bjFhZUVYdz09', 'Y1lxc0gzS1NDZjQxNkEwK0RmUTJqUT09', 'MlZpWVlvbXk5bDk3UHQxZGNJaWd4NGk4d2NLS21XdXhHYzFNWGJJblFQWT0=', 'TVFOWko1R2xMaGQvNXA0K3lWNGtFUT09', 'cXJSK0dVeUtzSW80dkdIM0xDWUxZQT09', 'aGdnNkdDSDRtdm9mK0VBcjR0YUtUZz09', 'M29zUndtMDRoRUZoQ2tSRHNTZ3grN010VzNBMmV6WGsrQytTY2E5Z2JTST0=', 'M1AzQ2cySEJtbUFyQWFUVkQyLzJsUT09', '2022-08-25 00:00:00', 0),
(17, 'SDd6OE1YUFBBeHUwQWJuM1NVT1VsQT09', 'enhSdDZycC9VTGlvc2IwT0Z2SjRXQT09', 'UTJmWHBkeDhNVWQwa1h5L1R0Nisza1ZPakRxMHVTa2VnNHhCVWh2dDJMVT0=', 'b1ppUzhIMUJyVVlKVEVDYi9pWElnK1FLaVRjaTVibll4T1hsY0ZxWjFTdz0=', 'c045YlkyREtuZXA0K3UwOWRzQnBTdz09', 'S0NkWWYvTUphSVNIb2hjMjdIbHpFdz09', 'MmlXWUovanpYSUhOQVl3dkRPWE5IQT09', 'VWFvcXQ1VXEvaWRQd0pPUW1WRjdRUT09', '2022-08-25 00:00:00', 0),
(18, 'SDd6OE1YUFBBeHUwQWJuM1NVT1VsQT09', 'enhSdDZycC9VTGlvc2IwT0Z2SjRXQT09', 'UTJmWHBkeDhNVWQwa1h5L1R0Nisza1ZPakRxMHVTa2VnNHhCVWh2dDJMVT0=', 'b1ppUzhIMUJyVVlKVEVDYi9pWElnK1FLaVRjaTVibll4T1hsY0ZxWjFTdz0=', 'c045YlkyREtuZXA0K3UwOWRzQnBTdz09', 'bmZBUktiNEtLNHgvQVlMWHV6K1lVUT09', 'MmlXWUovanpYSUhOQVl3dkRPWE5IQT09', 'VWFvcXQ1VXEvaWRQd0pPUW1WRjdRUT09', '2022-08-25 00:00:00', 0),
(19, 'ZFhkQ09FVmtoR3lIMWpaSW9SSkE4Uk5iSXZ1Q2J3VHhYNFZubkE4cThSVT0=', 'K2ljc09hTzZoVkJYY3Y3NHdzeDZFQT09', 'ZFhkQ09FVmtoR3lIMWpaSW9SSkE4Uk5iSXZ1Q2J3VHhYNFZubkE4cThSVT0=', 'YlNETTlqSmVPSkluSVJpZXc0eXRNQ2FKVWRkMUs3aXY3UlBtWTUvRVZJbz0=', 'UDIrdEJ1cDlXZDVyd2hQVFNMYis3Zz09', 'SXdVQ2xKd2tXNmE1QXUyaHkzS1pwdz09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'ZWozRXJ5VUk2bUppU2NuMmZoVm9CZz09', '2022-08-25 00:00:00', 0),
(20, 'SDd6OE1YUFBBeHUwQWJuM1NVT1VsQT09', 'enhSdDZycC9VTGlvc2IwT0Z2SjRXQT09', 'UTJmWHBkeDhNVWQwa1h5L1R0Nisza1ZPakRxMHVTa2VnNHhCVWh2dDJMVT0=', 'b1ppUzhIMUJyVVlKVEVDYi9pWElnK1FLaVRjaTVibll4T1hsY0ZxWjFTdz0=', 'c045YlkyREtuZXA0K3UwOWRzQnBTdz09', 'YS9jKzJTcldUMlFoT0xmTjVJVzVmQT09', 'MmlXWUovanpYSUhOQVl3dkRPWE5IQT09', 'VWFvcXQ1VXEvaWRQd0pPUW1WRjdRUT09', '2022-08-25 00:00:00', 0);

ALTER TABLE `contestants`
  ADD KEY `idx_email` (`email`);
COMMIT;
mariadb

2条答案

按热度按时间
eyh26e7m

eyh26e7m1#

查询query中没有任何内容指示电子邮件应该随机选择，您只为每封电子邮件随机选择一条记录。由于内部执行路径在正确的时间导致随机排序，这可能是运气使然，但无法保证查询会执行您所期望的操作。
考虑以下示例数据（忽略日期）
| 识别码|电邮地址|优胜者|
| - -|- -|- -|
| 一个|a@b.c|第0页|
| 2个|a@b.c|第0页|
| 三个|扣除扣除费用|第0页|
| 四个|扣除扣除费用|一个|
| 五个|x@y.z|第0页|
如果要将行号分配给新列并应用筛选器，则行号函数将获得两种可能的结果，具体取决于rand函数的结果：
| 识别码|电邮地址|优胜者|核糖核酸1|核糖核酸2|
| - -|- -|- -|- -|- -|
| 一个|a@b.c|第0页|一个|2个|
| 2个|a@b.c|第0页|2个|一个|
| 三个|扣除扣除费用|第0页|一个|一个|
| 五个|x@y.z|第0页|一个|一个|
这里的问题是，您没有进一步的排序，因此，虽然您可以确保第1行出现在第2行之前（反之亦然），其中的电子邮件是相同的，但没有任何排序逻辑可以指定任何其他行的顺序。您可能需要进一步排序结果，例如：

ORDER BY 
  ROW_NUMBER() OVER (
    PARTITION BY email 
    ORDER BY 
      RAND()
  ),
  RAND()

另一个问题，你有与上述是，如果你选择了前10名，无论你做什么，你会得到a@b.c两次，因为只有4个记录可供选择。
我也不认为你的查询反映了以下标准（强调我的）：
仅从没有任何获胜代码的参赛者中进行选择（winner = 1）。
在我给出的示例数据中，我认为d@e.f就是一个例子--一行有中奖代码，但另一行还没有中奖，因此仍有资格在当前规则下的下一轮中奖中被选中。
如果以上是一个问题，那么我认为你想要以下：

SELECT  c.ID, c.Email
FROM    (   SELECT  c.ID, 
                    c.Email,
                    MAX(c.winner) OVER(PARTITION BY c.email) AS HasWinner,
                    ROW_NUMBER() OVER(PARTINTION BY c.email ORDER BY RAND()) AS RowNumber
            FROM    contestants AS c
            WHERE   c.date >= '2022-08-01'
            AND     c.date < '2022-09-01'
        ) AS c
WHERE   c.HasWinner = 0 -- No Winners
AND     c.RowNumber = 1 -- First Row per Email
ORDER BY RAND()
LIMIT 10;

如果这不是问题，您可以简单地将winner上的where子句添加回内部查询，并删除外部查询上的筛选器（以及它从内部查询中筛选的列）

赞(0）分享  回复(0）举报  2022-11-08
ao218c7q

ao218c7q2#

请考虑

DROP TABLE IF EXISTS T;
CREATE TABLE T
(EMAIL VARCHAR(3),WINNER INT);
INSERT INTO T VALUES
('AAA',0),('BBB',0),('CCC',0),('AAA',0);

带限制4

SELECT 
  c.* ,ROW_NUMBER() OVER (PARTITION BY email ORDER BY RAND()) rn,
  RAND(ROW_NUMBER() OVER (PARTITION BY email ORDER BY RAND())) rnrand
FROM 
  T c 
WHERE 
  #(
  #  date BETWEEN '2022-08-01 00:00:00' 
  #  AND '2022-08-31 23:59:59'
  #) 
  #AND 
  winner = 0
ORDER BY ROW_NUMBER() OVER (PARTITION BY email ORDER BY RAND()) 
LIMIT 4;

+-------+--------+----+---------------------+
| EMAIL | WINNER | rn | rndand              |
+-------+--------+----+---------------------+
| BBB   |      0 |  1 | 0.40540353712197724 |
| CCC   |      0 |  1 | 0.40540353712197724 |
| AAA   |      0 |  1 | 0.40540353712197724 |
| AAA   |      0 |  2 |  0.6555866465490187 |
+-------+--------+----+---------------------+
4 rows in set (0.001 sec)

限制2

+-------+--------+----+---------------------+
| EMAIL | WINNER | rn | rnrand              |
+-------+--------+----+---------------------+
| BBB   |      0 |  1 | 0.40540353712197724 |
| CCC   |      0 |  1 | 0.40540353712197724 |
+-------+--------+----+---------------------+
2 rows in set (0.001 sec)
赞(0）分享  回复(0）举报  2022-11-08
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com