android - 统计不同列中不同数字的出现次数

Question

首先，很抱歉，如果这是一个重复的问题......

我有一个检查彩票游戏的 Android 应用程序 (SQLITE)。基本上，这个彩票在 40 个中抽取 5 个号码，我将它们保存如下:

|---------------------------------------------------------------------------|
|                               TABLE: lotery                               |
|---------------------------------------------------------------------------|
|   game_number |   NUM1    |   NUM2    |   NUM3    |   NUM4    |   NUM5    |
|---------------------------------------------------------------------------|
|       1       |    02     |    32     |    33     |    34     |    40     |
|       2       |    02     |    14     |    16     |    39     |    40     |
|       3       |    01     |    11     |    12     |    25     |    27     |
|       4       |    02     |    22     |    23     |    30     |    36     |
|                                   ......                                  |
|---------------------------------------------------------------------------|

现在，我想咨询一个数字被选中的频率。

例子:

• 01 号被抽到 1 次(在 game_number==3 中)
• 02 号抽了 3 次(在 game_number==1，game_number==2 和 game_number==4))
• 等等...

我今天有什么:

SELECT * FROM
        (select count(*) FROM lotery where 1 IN(NUM1,NUM2,NUM3,NUM4,NUM5)),
        (select count(*) FROM lotery where 2 IN(NUM1,NUM2,NUM3,NUM4,NUM5)),
        (select count(*) FROM lotery where 3 IN(NUM1,NUM2,NUM3,NUM4,NUM5)),
        ....
        (select count(*) FROM lotery where 40 IN(NUM1,NUM2,NUM3,NUM4,NUM5));

这将产生:

|-----------------------------------------------------------|
|  count(*) |  count(*) |  count(*) |  ......   |  count(*) |
|-----------------------------------------------------------|
|     1     |     3     |     0     |  ......   |     2     |
|-----------------------------------------------------------|

第一列对应“01”。第二列对应于数字“02”。等等……直到第 40 列

最后是问题

当前的实现在一些旧的和低成本的设备上性能低下。

所以，我有以下问题:

1. 您是否看到针对当前表实现的更好的查询？

2. 是否可以查询并获得排序的结果？

今天，我执行该查询并按 DESC 顺序手动对结果进行排序。我的 Activity 以 DESC 顺序在 ListView 中显示结果。

因为我使用的是 ListView ，所以我不需要一次获得所有结果。例如，我可以仅查询前 10 个更频繁的数字(以填满屏幕)，并且仅在用户滚动 ListView 时查询剩余数字。

无论如何，如果您发现当前实现有任何可能的改进，我将不胜感激...

提前致谢!

Answer 1

1. Do you see a better query for current table implementation?

是的，检查下面:)

2.Is it possible to query and get the results ordered?

是的。

---

您可以使用计数表和LEFT JOIN:

WITH num(n) AS
(
   VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
         ,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
         ,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
         ,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40)
)
SELECT num.n, COUNT(l.game_number) AS cnt
FROM num 
LEFT JOIN lottery l
  ON num.n IN (l.NUM1, l.NUM2,l.NUM3, l.NUM4,l.NUM5)
GROUP BY num.n
ORDER BY cnt DESC, num.n;

SqlFiddleDemo

输出:

╔═════╦═════╗
║ n   ║ cnt ║
╠═════╬═════╣
║  2  ║   3 ║
║ 40  ║   2 ║
║  1  ║   1 ║
║ 11  ║   1 ║
║ 12  ║   1 ║
║ 14  ║   1 ║
║ 16  ║   1 ║
║ 22  ║   1 ║
║ 23  ║   1 ║
║ 25  ║   1 ║
║ 27  ║   1 ║
║ 30  ║   1 ║
║ 32  ║   1 ║
║ 33  ║   1 ║
║ 34  ║   1 ║
║ 36  ║   1 ║
║ 39  ║   1 ║
║  3  ║   0 ║
║  4  ║   0 ║
║  5  ║   0 ║
║  6  ║   0 ║
║  7  ║   0 ║
║  8  ║   0 ║
║  9  ║   0 ║
║ 10  ║   0 ║
║ 13  ║   0 ║
║ 15  ║   0 ║
║ 17  ║   0 ║
║ 18  ║   0 ║
║ 19  ║   0 ║
║ 20  ║   0 ║
║ 21  ║   0 ║
║ 24  ║   0 ║
║ 26  ║   0 ║
║ 28  ║   0 ║
║ 29  ║   0 ║
║ 31  ║   0 ║
║ 35  ║   0 ║
║ 37  ║   0 ║
║ 38  ║   0 ║
╚═════╩═════╝

编辑:

it seems that SQLITE3 embed in Android API16 is v3.7.11 Do you see any limitation by using you answer?

如果不支持 WITH，您可以使用子查询轻松更改它:

SELECT num.n, COUNT(l.game_number) AS cnt
FROM (SELECT 1 AS n
      UNION SELECT 2
      UNION SELECT 3
       -- ... up to 40
      ) num
LEFT JOIN lottery l
  ON num.n IN (l.NUM1, l.NUM2,l.NUM3, l.NUM4,l.NUM5)
GROUP BY num.n
ORDER BY cnt DESC, num.n;

SqlFiddleDemo2