mysql之使用 Count 获取在 HTML 列中找到的最常见的前 10 个项目

我有一个有点乱的查询要弄清楚。

我有一个名为“meta_value”的列，其中有一些 HTML 数据，例如:

<tr class="child-row123"><td class="monsters">Monster</td><td class="monsters"><a data-name="Zure, Knight of Dark World">Zure, Knight of Dark World</a> x1</br><a data-name="Dark Necrofear">Dark Necrofear</a> x1</br><a data-name="Grapha, Dragon Lord of Dark World">Grapha, Dragon Lord of Dark World</a> x3</br><a data-name="Reign-Beaux, Overlord of Dark World">Reign-Beaux, Overlord of Dark World</a> x1</br><a data-name="Goldd, Wu-Lord of Dark World">Goldd, Wu-Lord of Dark World</a> x1</br><a data-name="Sillva, Warlord of Dark World">Sillva, Warlord of Dark World</a> x3</br><a data-name="Beiige, Vanguard of Dark World">Beiige, Vanguard of Dark World</a> x2</br><a data-name="Brron, Mad King of Dark World">Brron, Mad King of Dark World</a> x3</br><a data-name="Trance Archfiend">Trance Archfiend</a> x3</br><a data-name="Kahkki, Guerilla of Dark World">Kahkki, Guerilla of Dark World</a> x3</br><a data-name="Scarr, Scout of Dark World">Scarr, Scout of Dark World</a> x2</br></td></tr><tr class="child-row123"><td class="spells">Spells</td><td class="spells"><a data-name="Dark Core">Dark Core</a> x1</br><a data-name="Dark World Dealings">Dark World Dealings</a> x2</br><a data-name="Dark World Lightning">Dark World Lightning</a> x3</br><a data-name="The Gates of Dark World">The Gates of Dark World</a> x3</br><a data-name="Gateway to Dark World">Gateway to Dark World</a> x2</br></td></tr><tr class="child-row123"><td class="traps">Traps</td><td class="traps"><a data-name="Dark Scheme">Dark Scheme</a> x1</br><a data-name="The Forces of Darkness">The Forces of Darkness</a> x1</br><a data-name="Dark Smog">Dark Smog</a> x1</br><a data-name="Dark World Brainwashing">Dark World Brainwashing</a> x3</br><a data-name="Depth Amulet">Depth Amulet</a> x1</br></td></tr><tr class="child-row123"><td class="extra">Extra</td><td class="extra"><a data-name="Number 23: Lancelot, Dark Knight of the Underworld">Number 23: Lancelot, Dark Knight of the Underworld</a> x1</br></td></tr><tr class="child-row123"><td class="id">Id</td><td class="id"></td></tr> 

我有 6000 多个这样的行，我试图在不杀死服务器的情况下找到其中最常见的前 10 个值。

例如，我可以通过执行以下操作找到名为“LANphorhynchus”的卡片出现了多少次:

SELECT COUNT(DISTINCT(meta_value)) 
FROM wph3_postmeta 
WHERE meta_value LIKE "%>LANphorhynchus</a>%" 
AND meta_key = "deck_list" 

太棒了!但我想尝试找出前 10 名，而我并不知道前 10 名中有哪些名字。

这是否应该在 MySQL 中执行？

澄清:

如前所述，meta_value 列包含完整的 HTML 标记。每张卡片的名称都插入此处:

<a data-name="LANphorhynchus">LANphorhynchus</a> 

并且每一行在此 meta_value 列中都有多个卡片名称(有关行中数据的完整示例，请参见顶部)。所以我在 MySQL 中搜索它们 "%>LANphorhynchus</a>%"如果我想要一张特定的卡片(我需要在标签之间搜索)。

但是。我想尝试从我不知道卡片名称的所有行中获取此列中找到的前 10 张卡片。 所以我可能认为可能需要一些 regex 来在每行的标签之间进行搜索并找到最常出现的卡片？

数据库行的摘录:https://docs.google.com/spreadsheets/d/1625ha8zGlCGvVnRa8N3cwpqVqg1JxF5mT8i04CENRWc/edit?usp=sharing

回答: 感谢 Midwinter86，我能够从一个新的角度来看待这个问题。

我所做的如下。

Run a query on top 100 most viewed cards in my database and insert the names into an array.

Within a loop, check each of those names specifically against the meta_values table to see how often they appear (using my LANphorhynchus example above).

Extract all data to a new table top_10_cards.

Cache data for 1 week and then re-run.

输出:

+-----------------------------+---------+ 
|            name             |  count  | 
+-----------------------------+---------+ 
| Monster Reborn              |    2842 | 
| Ash Blossom & Joyous Spring |    2587 | 
| Knightmare Phoenix          |    2231 | 
| Borreload Dragon            |    1975 | 
+-----------------------------+---------+ 

请您参考如下方法:

以下是纯粹的 MySQL-only 解决方案；您可以在非高峰时段每天运行一次(或两次)此查询，以更新缓存/汇总表中的计数。此外，行数大约为 6000(仅)，因此(取决于您的服务器配置)应该不会导致性能问题。

现在，由于特定行中的卡片数量是可变的(范围为 40-60)，我们可以使用序列表。您可以在数据库中定义一个永久表，用于存储 1 到 100 之间的整数(您可能发现此表在许多其他情况下也很有用):

CREATE TABLE seq (n tinyint(3) UNSIGNED NOT NULL, PRIMARY KEY(n)); 
INSERT INTO seq (n) VALUES (1), (2), ...... , (99), (100); 

现在，我们将根据子字符串 'data 的出现次数在 wph3_postmeta 和 seq 表之间做一个JOIN -name=""' 在特定的 meta_value 中。 We can get the count of occurrence of the substring (这也意味着，特定行中的卡片计数)使用:

( 
  CHAR_LENGTH(wp.meta_value)  
  - CHAR_LENGTH(REPLACE(wp.meta_value, 'data-name=""', '')) 
) / CHAR_LENGTH('data-name=""') 
 

现在，我们可以使用 Substring_Index()提取卡值的函数。使用不同行的不同数字，我们基本上可以提取出第一张牌，第二张牌，依此类推...

一旦我们将所有单词提取出来，在不同的行中；然后我们可以使用完整的结果集作为 Derived Table ，并执行聚合查询以获得所需的结果:

查询(View on DB Fiddle)

SELECT dt.name, 
       Count(DISTINCT dt.meta_id) AS unique_metaid_count 
FROM   (SELECT wp.meta_id, 
               Substring_index(Substring_index(wp.meta_value, 'data-name=""', 
                               -seq.n), 
               '"">', 1 
               ) AS name 
        FROM   wph3_postmeta AS wp 
               JOIN seq 
                 ON ( Char_length(wp.meta_value) - Char_length( 
                                                   REPLACE(wp.meta_value, 
                                                   'data-name=""' 
                                                        , 
                                                        '')) ) / 
                         Char_length('data-name=""') >= n 
        WHERE  wp.meta_key = 'deck_list') AS dt 
GROUP  BY dt.name 
ORDER  BY unique_metaid_count DESC   
/* To get top 10 counts only, add LIMIT 10 */ 

结果

| name                                          | unique_metaid_count | 
| --------------------------------------------- | ------------------- | 
| Call of the Haunted                           | 2                   | 
| Inferno Reckless Summon                       | 2                   | 
| Mystic Box                                    | 2                   | 
| Mystical Space Typhoon                        | 2                   | 
| Number 39: Utopia                             | 2                   | 
| #created by ygopro2                           | 1                   | 
| 98095162                                      | 1                   | 
| Abyss Dweller                                 | 1                   | 
| Advanced Ritual Art                           | 1                   | 
| Armed Dragon LV3                              | 1                   | 
| Armed Dragon LV5                              | 1                   | 
| Axe of Despair                                | 1                   | 
| B.E.S. Covered Core                           | 1                   | 
..... 
 
| The Dragon Dwelling in the Cave               | 1                   | 
| The Flute of Summoning Dragon                 | 1                   | 
| The Forces of Darkness                        | 1                   | 
| Threatening Roar                              | 1                   | 
| Time Machine                                  | 1                   | 
| Torike                                        | 1                   | 
| Tornado Dragon                                | 1                   | 
| Torrential Tribute                            | 1                   | 
| Tragoedia                                     | 1                   | 
| Trap Hole                                     | 1                   | 
| Treeborn Frog                                 | 1                   | 
| Trishula, Dragon of the Ice Barrier           | 1                   | 
| Twin Twisters                                 | 1                   | 
| Vanity's Ruler                                | 1                   | 
| Wind-Up Snail                                 | 1                   | 
| Wind-Up Soldier                               | 1                   | 
| Wulf, Lightsworn Beast                        | 1                   | 
| Zure, Knight of Dark World                    | 1                   | 

注意:如果您只想要前 10 个(按计数)，您只需在查询末尾添加 LIMIT 10。