MySQL查詢優化-添加一個具有組最大值的新列。應該為整個組重復該值

我有一個 mysql 表，其中包含以下列 - 日期、國家/地區、年份、月份、超類別、類、corp_manufacturer、品牌、許可證、專案描述、數量、單位、arp。

記錄總數 = 710 萬

現在我想添加一個新列，顯示組的 arp 最大值（國家、年、月、超類別、類、corp_manufacturer、品牌、許可證、item_description）。必須為組中的所有成員重復此最大值。

我嘗試了以下兩種方法。兩者都有效，但它們都需要很長時間才能更新資料超過 2 小時。

我正在尋找可以更快地做到這一點的任何更好的解決方案。請幫忙。

方法 1- 從子查詢更新表

UPDATE table a
INNER JOIN (SELECT Country,YEAR, MONTH, supercategory, class, corp_manufacturer, brand, license, item_description, MAX(arp) AS max_arp FROM table GROUP BY 1,2,3,4,5,6,7,8,9) b  

on a.country = b.country AND a.year = b.year AND a.month=b.month AND a.supercategory=b.supercategory AND a.class=b.class AND a.corp_manufacturer=b.corp_manufacturer AND a.brand=b.brand AND a.license=b.license AND a.item_description = b.item_description

SET a.max_arp= b.max_arp

方法 2 - 創建帶有索引的臨時表并將它們加入到新表中

INSERT INTO  temp1
(SELECT Country,YEAR, MONTH, supercategory, class, corp_manufacturer, brand, license, item_description, MAX(arp) AS max_arp 
    FROM table 
    GROUP BY 1,2,3,4,5,6,7,8,9);

INSERT IGNORE INTO  temp2
SELECT a.*,b.max_arp AS SRP FROM  table  a  JOIN  temp1 b
ON a.country = b.country AND a.year = b.year AND a.month=b.month AND a.supercategory=b.supercategory AND a.class=b.class AND a.corp_manufacturer=b.corp_manufacturer 
    AND a.brand=b.brand AND a.license=b.license AND a.item_description = b.item_description;

uj5u.com熱心網友回復：

我們可以使用視窗函式計算組數。也就是說，當您可以只使用視圖時，為什么要創建列：

create myview v as
select t.*,
    max(arp) over(partition by 
        country, 
        year, 
        month, 
        supercategory, 
        class, 
        corp_manufacturer, 
        brand, 
        license,
        item_description
    ) max_arp
from mytable t

使用這種技術，您可以獲得始終最新的資料透視圖，而維護成本為 0。請注意，這需要 MySQL 8.0。

如果您堅持存盤資訊，那么在 MySQL 中我會推薦更新/連接語法。假設您的表有一個名為的主鍵id：

update mytable t
inner join (
    select id,
        max(arp) over(partition by 
            country, 
            year, 
            month, 
            supercategory, 
            class, 
            corp_manufacturer, 
            brand, 
            license,
            item_description
        ) max_arp
    from mytable t
) x on x.id = t.id
set t.max_arp = x.max_arp

uj5u.com熱心網友回復：

使用所需的 arp 值創建一個表：

CREATE TABLE the_groups (
    PRIMARY KEY (Country,YEAR, MONTH, supercategory,
                 class, corp_manufacturer,
                 brand, license, item_description)
    ) AS
  SELECT  Country,YEAR, MONTH, supercategory,
          class, corp_manufacturer,
          brand, license, item_description,
          MAX(arp) AS max_arp
    FROM  table
    GROUP BY  1,2,3,4,5,6,7,8,9;

（這比快得多UPDATE。）

如有必要，您可以保留，但無論何時修改the_groups，您都需要維護它。tables

同時，您可以通過以下方式獲得您所要求的效果

CREATE VIEW table_plus_maxarp AS
    SELECT a.*, b.max_arp
        FROM `table` AS a
        JOIN `the_groups` AS b
            USING (Country,YEAR, MONTH, supercategory,
                   class, corp_manufacturer,
                   brand, license, item_description)
           ;

uj5u.com熱心網友回復：

我認為如果您的表具有以下兩個索引會更好：

1. 欄位索引arp。
2. 欄位索引Country, YEAR, MONTH, supercategory, class, corp_manufacturer, brand, license, item_description。

之后，您可以使用任何您想要的東西。

temp1對于使用臨時表，如果具有與表中相同的索引會更快Country, YEAR, MONTH, ....。您查詢的第二部分將是：

    UPDATE table a, temp1 b ON 
    a.country = b.country AND a.year = b.year AND a.month=b.month AND  
    a.supercategory=b.supercategory AND a.class=b.class AND  
    a.corp_manufacturer=b.corp_manufacturer AND a.brand=b.brand AND  
    a.license=b.license AND a.item_description = b.item_description
        
    SET a.max_arp= b.max_arp;

標籤：mysqlsqlsql更新最大限度聚合函数

上一篇：查詢varchar日期時間戳并按日期降序排序

下一篇：獲取參考中的行數