表中每列的频率列表

Question

I have table that looks like this:

r01 r02 r03 r04
1   2   X   X
1   2   X   1
X   1   2   1
X   2   2   2
1   2   1   X
1   1   1   2
1   X   1   1
1   2   X   1
2   2   X   2

And I would like to get a frequency array list for each column (not row), similar to array_count_values(). Like

r01: 1 => 6, X => 2, 2 => 1
r02: 1 => 2, X => 1, 2 => 6
r03: 1 => 3, X => 4, 2 => 2
r04: 1 => 4, X => 2, 2 => 3

Is it possible to to with one or a few mysql questions? I have not come up with and idea. The only solution I has is to get all data to PHP and then have a data array and just add a one to corresponding counter for each row.

I can have 100->20000 rows. So I would like to have a mysql solution that scales better that a PHP solution.

-- Edit

I was displaying a simplified table structure but I think I need to show the full table.

CREATE TABLE IF NOT EXISTS `tips_rows` (
  `row_id` int(11) NOT NULL,
  `r01` enum('1','X','2') NOT NULL,
  `r02` enum('1','X','2') NOT NULL,
  `r03` enum('1','X','2') NOT NULL,
  `r04` enum('1','X','2') NOT NULL,
  `r05` enum('1','X','2') NOT NULL,
  `r06` enum('1','X','2') NOT NULL,
  `r07` enum('1','X','2') NOT NULL,
  `r08` enum('1','X','2') NOT NULL,
  `r09` enum('1','X','2') NOT NULL,
  `r10` enum('1','X','2') NOT NULL,
  `r11` enum('1','X','2') NOT NULL,
  `r12` enum('1','X','2') NOT NULL,
  `r13` enum('1','X','2') NOT NULL,
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And tips_rows contains all combinations of the values. (1,6 million rows) And to this I have a link table connection user to tips_rows, ´tips_rows_users´

So this link table would hold for a number of users a data set of 100-10000 row_id for each user.

I have found one solution base on other stackoverflow googling. Maybe not the purest query but it works and is fast.

 SELECT
    SUM(CASE WHEN r01 = '1' THEN 1 ELSE 0 END) AS r11,
    SUM(CASE WHEN r01 = 'X' THEN 1 ELSE 0 END) AS r1X,
    SUM(CASE WHEN r01 = '2' THEN 1 ELSE 0 END) AS r12,
    SUM(CASE WHEN r02 = '1' THEN 1 ELSE 0 END) AS r21,
    SUM(CASE WHEN r02 = 'X' THEN 1 ELSE 0 END) AS r2X,
    SUM(CASE WHEN r02 = '2' THEN 1 ELSE 0 END) AS r22,
    SUM(CASE WHEN r03 = '1' THEN 1 ELSE 0 END) AS r31,
    SUM(CASE WHEN r03 = 'X' THEN 1 ELSE 0 END) AS r3X,
    SUM(CASE WHEN r03 = '2' THEN 1 ELSE 0 END) AS r32,
    SUM(CASE WHEN r04 = '1' THEN 1 ELSE 0 END) AS r41,
    SUM(CASE WHEN r04 = 'X' THEN 1 ELSE 0 END) AS r4X,
    SUM(CASE WHEN r04 = '2' THEN 1 ELSE 0 END) AS r42,
    SUM(CASE WHEN r05 = '1' THEN 1 ELSE 0 END) AS r51,
    SUM(CASE WHEN r05 = 'X' THEN 1 ELSE 0 END) AS r5X,
    SUM(CASE WHEN r05 = '2' THEN 1 ELSE 0 END) AS r52,
    SUM(CASE WHEN r06 = '1' THEN 1 ELSE 0 END) AS r61,
    SUM(CASE WHEN r06 = 'X' THEN 1 ELSE 0 END) AS r6X,
    SUM(CASE WHEN r06 = '2' THEN 1 ELSE 0 END) AS r62,
    SUM(CASE WHEN r07 = '1' THEN 1 ELSE 0 END) AS r71,
    SUM(CASE WHEN r07 = 'X' THEN 1 ELSE 0 END) AS r7X,
    SUM(CASE WHEN r07 = '2' THEN 1 ELSE 0 END) AS r72,
    SUM(CASE WHEN r08 = '1' THEN 1 ELSE 0 END) AS r81,
    SUM(CASE WHEN r08 = 'X' THEN 1 ELSE 0 END) AS r8X,
    SUM(CASE WHEN r08 = '2' THEN 1 ELSE 0 END) AS r82,
    SUM(CASE WHEN r09 = '1' THEN 1 ELSE 0 END) AS r91,
    SUM(CASE WHEN r09 = 'X' THEN 1 ELSE 0 END) AS r9X,
    SUM(CASE WHEN r09 = '2' THEN 1 ELSE 0 END) AS r92,
    SUM(CASE WHEN r10 = '1' THEN 1 ELSE 0 END) AS r101,
    SUM(CASE WHEN r10 = 'X' THEN 1 ELSE 0 END) AS r10X,
    SUM(CASE WHEN r10 = '2' THEN 1 ELSE 0 END) AS r102,
    SUM(CASE WHEN r11 = '1' THEN 1 ELSE 0 END) AS r111,
    SUM(CASE WHEN r11 = 'X' THEN 1 ELSE 0 END) AS r11X,
    SUM(CASE WHEN r11 = '2' THEN 1 ELSE 0 END) AS r112,
    SUM(CASE WHEN r12 = '1' THEN 1 ELSE 0 END) AS r121,
    SUM(CASE WHEN r12 = 'X' THEN 1 ELSE 0 END) AS r12X,
    SUM(CASE WHEN r12 = '2' THEN 1 ELSE 0 END) AS r122,
    SUM(CASE WHEN r13 = '1' THEN 1 ELSE 0 END) AS r131,
    SUM(CASE WHEN r13 = 'X' THEN 1 ELSE 0 END) AS r13X,
    SUM(CASE WHEN r13 = '2' THEN 1 ELSE 0 END) AS r132
    FROM `tips_rows` AS r
    INNER JOIN tips_rows_users USING (row_id)
    WHERE user__id='{userid}'

This will give me one result row as

r11 r1X r12 r21 r2X r22 r31 r3X r32 r41 r4X r42 r51 r5X r52 r61 r6X r62 r71 r7X r72 r81 r8X r82 r91 r9X r92 r101 r10X r102 r111 r11X r112 r121 r12X r122 r131 r13X r132
40  34  26  48  30  22  69  14  17  70  16  14  15  17  68  28  31  41  80  20  0   49  29  22  38  30  32  69   16   15   29   28   43   19   31   50   13   25   62

And that I can use in my php-template file.

Answer 1

Thanks for all the different solution. I have now done some testing on my data. I have done a query for 10000rows. And here is my findings.

Solution 1:

Select 'R01', r01, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r01
UNION 
Select 'R02', r02, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r02 
UNION
Select 'R03', r03, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r03 
UNION
Select 'R04', r04, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r04 
UNION
Select 'R05', r05, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r05 
UNION
Select 'R06', r06, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r06 
UNION
Select 'R07', r07, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r07 
UNION
Select 'R08', r08, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r08 
UNION
Select 'R09', r09, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r09 
UNION
Select 'R10', r10, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r10 
UNION
Select 'R11', r11, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r11 
UNION
Select 'R12', r12, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r12 
UNION
Select 'R13', r13, count(1) FROM `tips_rows` AS r INNER JOIN tips_rows_users USING (row_id) WHERE user_id='27' group by r13 

Takes ~0.1365s every time to query

Solution 2:

SELECT n,
       SUM(IF(r01 = n, 1, 0)) AS 'r01',
       SUM(IF(r02 = n, 1, 0)) AS 'r02',
       SUM(IF(r03 = n, 1, 0)) AS 'r03',
       SUM(IF(r04 = n, 1, 0)) AS 'r04',
       SUM(IF(r05 = n, 1, 0)) AS 'r05',
       SUM(IF(r06 = n, 1, 0)) AS 'r06',
       SUM(IF(r07 = n, 1, 0)) AS 'r07',
       SUM(IF(r08 = n, 1, 0)) AS 'r08',
       SUM(IF(r09 = n, 1, 0)) AS 'r09',
       SUM(IF(r10 = n, 1, 0)) AS 'r10',
       SUM(IF(r11 = n, 1, 0)) AS 'r11',
       SUM(IF(r12 = n, 1, 0)) AS 'r12',
       SUM(IF(r13 = n, 1, 0)) AS 'r13'       
FROM `tips_rows` AS r
INNER JOIN tips_rows_users USING (row_id)
JOIN (SELECT '1' AS n UNION SELECT '2' UNION SELECT 'X') S
WHERE user_id='27'
GROUP BY n;

Takes ~0.0997s first time to query. Could be cached in mysql query cache so second time takes ~0.0002s

Solution 3:

SELECT
    SUM(CASE WHEN r01 = '1' THEN 1 ELSE 0 END) AS r11,
    SUM(CASE WHEN r01 = 'X' THEN 1 ELSE 0 END) AS r1X,
    SUM(CASE WHEN r01 = '2' THEN 1 ELSE 0 END) AS r12,
    SUM(CASE WHEN r02 = '1' THEN 1 ELSE 0 END) AS r21,
    SUM(CASE WHEN r02 = 'X' THEN 1 ELSE 0 END) AS r2X,
    SUM(CASE WHEN r02 = '2' THEN 1 ELSE 0 END) AS r22,
    SUM(CASE WHEN r03 = '1' THEN 1 ELSE 0 END) AS r31,
    SUM(CASE WHEN r03 = 'X' THEN 1 ELSE 0 END) AS r3X,
    SUM(CASE WHEN r03 = '2' THEN 1 ELSE 0 END) AS r32,
    SUM(CASE WHEN r04 = '1' THEN 1 ELSE 0 END) AS r41,
    SUM(CASE WHEN r04 = 'X' THEN 1 ELSE 0 END) AS r4X,
    SUM(CASE WHEN r04 = '2' THEN 1 ELSE 0 END) AS r42,
    SUM(CASE WHEN r05 = '1' THEN 1 ELSE 0 END) AS r51,
    SUM(CASE WHEN r05 = 'X' THEN 1 ELSE 0 END) AS r5X,
    SUM(CASE WHEN r05 = '2' THEN 1 ELSE 0 END) AS r52,
    SUM(CASE WHEN r06 = '1' THEN 1 ELSE 0 END) AS r61,
    SUM(CASE WHEN r06 = 'X' THEN 1 ELSE 0 END) AS r6X,
    SUM(CASE WHEN r06 = '2' THEN 1 ELSE 0 END) AS r62,
    SUM(CASE WHEN r07 = '1' THEN 1 ELSE 0 END) AS r71,
    SUM(CASE WHEN r07 = 'X' THEN 1 ELSE 0 END) AS r7X,
    SUM(CASE WHEN r07 = '2' THEN 1 ELSE 0 END) AS r72,
    SUM(CASE WHEN r08 = '1' THEN 1 ELSE 0 END) AS r81,
    SUM(CASE WHEN r08 = 'X' THEN 1 ELSE 0 END) AS r8X,
    SUM(CASE WHEN r08 = '2' THEN 1 ELSE 0 END) AS r82,
    SUM(CASE WHEN r09 = '1' THEN 1 ELSE 0 END) AS r91,
    SUM(CASE WHEN r09 = 'X' THEN 1 ELSE 0 END) AS r9X,
    SUM(CASE WHEN r09 = '2' THEN 1 ELSE 0 END) AS r92,
    SUM(CASE WHEN r10 = '1' THEN 1 ELSE 0 END) AS r101,
    SUM(CASE WHEN r10 = 'X' THEN 1 ELSE 0 END) AS r10X,
    SUM(CASE WHEN r10 = '2' THEN 1 ELSE 0 END) AS r102,
    SUM(CASE WHEN r11 = '1' THEN 1 ELSE 0 END) AS r111,
    SUM(CASE WHEN r11 = 'X' THEN 1 ELSE 0 END) AS r11X,
    SUM(CASE WHEN r11 = '2' THEN 1 ELSE 0 END) AS r112,
    SUM(CASE WHEN r12 = '1' THEN 1 ELSE 0 END) AS r121,
    SUM(CASE WHEN r12 = 'X' THEN 1 ELSE 0 END) AS r12X,
    SUM(CASE WHEN r12 = '2' THEN 1 ELSE 0 END) AS r122,
    SUM(CASE WHEN r13 = '1' THEN 1 ELSE 0 END) AS r131,
    SUM(CASE WHEN r13 = 'X' THEN 1 ELSE 0 END) AS r13X,
    SUM(CASE WHEN r13 = '2' THEN 1 ELSE 0 END) AS r132
    FROM `tips_rows` AS r
    INNER JOIN tips_rows_users USING (row_id)
    WHERE user_id='27'

Takes ~0.0587s first and second time. After that 0.0002s for question after that.

I will go with solution 3. As it has the best worst case time. But it is strange that it takes two queries before cached.