MySQL 乘法子查询结果 [英] MySQL multiplying subquery results

问题描述

我有一个看起来像的数据表

<上一页>+---------+-----------+------------+------------+|u_id |a_id |计数 |加权 |+---------+-----------+------------+------------+|1 |1 |17 |0.0521472 ||1 |2 |80 |0.245399 ||1 |3 |2 |0.00613497 ||1 |4 |1 |0.00306748 ||1 |5 |1 |0.00306748 ||1 |6 |20 |0.0613497 ||1 |7 |3 |0.00920245 ||1 |8 |100 |0.306748 ||1 |9 |100 |0.306748 ||1 |10 |2 |0.00613497 ||2 |1 |1 |0.00327869 ||2 |2 |1 |0.00327869 ||2 |3 |100 |0.327869 ||2 |4 |200 |0.655738 ||2 |5 |1 |0.00327869 ||2 |6 |1 |0.00327869 ||2 |7 |0 |0 ||2 |8 |0 |0 ||2 |9 |0 |0 ||2 |10 |1 |0.00327869 ||3 |1 |15 |0.172414 ||3 |2 |40 |0.45977 ||3 |3 |0 |0 ||3 |4 |0 |0 ||3 |5 |0 |0 ||3 |6 |10 |0.114943 ||3 |7 |1 |0.0114943 ||3 |8 |20 |0.229885 ||3 |9 |0 |0 ||3 |10 |1 |0.0114943 |+---------+-----------+------------+------------+

可以用

重新创建<上一页>CREATE TABLE IF NOT EXISTS tablename (u_id INT NOT NULL, a_id MEDIUMINT NOT NULL,s_count MEDIUMINT NOT NULL, weighted FLOAT NOT NULL)ENGINE=INNODB;INSERT INTO tablename (u_id,a_id,s_count,weighted ) VALUES (1,1,17,0.0521472392638),(1,2,80,0.245398773006),(1,3,2,0.00613496932515),(1,4,1,0.00306748466258),(1,5,1,0.00306748466258),(1,6,20,0.0613496932515),(1,7,3,0.00920245398773),(1,8,100,0.306748466258),(1,4,8,460),(1,4,8,460),(1,4,8,460)1,10,2,0.00613496932515),(2,1,1,0.00327868852459),(2,2,1,0.00327868852459),(2,3,100,0.327868852459),(2,4,200,0.655737704918),(1,0.00327868852459),(2,6,1,0.00327868852459),(2,7,0,0.0),(2,8,0,0.0),(2,9,0,0.0),(2,10,1,0.00327868852459),(3,1,15,0.172413793103),(3,2,40,0.459770114943),(3,3,0,0.0),(3,4,0,0.0),(3,5,0,0.0),(3,6,10,0.114942528736),(3,7,1,0.0114942528736),(3,8,20,0.229885057471),(3,9,0,0.0),(3,10,1,0.0114942528736);

我想要做的简单版本是

SELECT u_id, SUM(weighted) as total FROM tablename WHERE a_id IN (1,2,3,4,5,6,7,8,9) GROUP BY u_id ORDER BY total DESC;

给出结果

<上一页>+---------+--------------------+|u_id |总计|+---------+--------------------+|2 |0.996721301227808 ||1 |0.993865059688687 ||3 |0.988505747169256 |+---------+--------------------+

我想做的更复杂的版本是根据来自 u_id 的计数对结果进行加权，因此从

中获取结果<上一页>查询 1SELECT count FROM tablename WHERE u_id = 1

会返回

<上一页>+-----------+------------+|a_id |计数 |+-----------+------------+|1 |17 ||2 |80 ||3 |2 ||4 |1 ||5 |1 ||6 |20 ||7 |3 ||8 |100 ||9 |100 ||10 |2 |+-----------+------------+

然后将用于计算总和，应该给出

+---------+-------------------+|u_id |总计|+---------+--------------------+|1 |83.15337423 ||3 |65.05747126 ||2 |1.704918033 |+---------+--------------------+

例如用 u_id =3 计算将由

完成

sum(查询 1 的计数值 * u_id 的加权值 = 每个 a_id 的 3)

<上一页>17 * 0.172413793 =2.93103448380 * 0.459770115 =36.78160922 * 0 =01 * 0 =01 * 0 =020 * 0.114942529 =2.2988505753 * 0.011494253 =0.034482759100 * 0.229885057 =22.98850575100 * 0 =02 * 0.011494253 =0.022988506总计为 65.05747126

我怎样才能用一个查询来做到这一点?

解决方案

您可以使用子查询来做到这一点.获取特定 id 计数的查询是:

SELECT a_id, s_count FROM tablename WHERE u_id = <id>

您将希望将此子查询的结果左连接到主表中，然后在适当的乘法上进行 sub ，如下所示:

SELECT u_id, SUM(counts.s_count * tablename.weighted) AS total FROM tablenameLEFT JOIN (SELECT a_id, s_count FROM tablename WHERE u_id = 1) 计数ON tablename.a_id = counts.a_id按 u_id 分组

I have a table of data that looks like

+---------+-----------+------------+------------+
| u_id    | a_id      | count      | weighted   |
+---------+-----------+------------+------------+
|       1 |         1 |         17 |  0.0521472 |
|       1 |         2 |         80 |   0.245399 |
|       1 |         3 |          2 | 0.00613497 |
|       1 |         4 |          1 | 0.00306748 |
|       1 |         5 |          1 | 0.00306748 |
|       1 |         6 |         20 |  0.0613497 |
|       1 |         7 |          3 | 0.00920245 |
|       1 |         8 |        100 |   0.306748 |
|       1 |         9 |        100 |   0.306748 |
|       1 |        10 |          2 | 0.00613497 |
|       2 |         1 |          1 | 0.00327869 |
|       2 |         2 |          1 | 0.00327869 |
|       2 |         3 |        100 |   0.327869 |
|       2 |         4 |        200 |   0.655738 |
|       2 |         5 |          1 | 0.00327869 |
|       2 |         6 |          1 | 0.00327869 |
|       2 |         7 |          0 |          0 |
|       2 |         8 |          0 |          0 |
|       2 |         9 |          0 |          0 |
|       2 |        10 |          1 | 0.00327869 |
|       3 |         1 |         15 |   0.172414 |
|       3 |         2 |         40 |    0.45977 |
|       3 |         3 |          0 |          0 |
|       3 |         4 |          0 |          0 |
|       3 |         5 |          0 |          0 |
|       3 |         6 |         10 |   0.114943 |
|       3 |         7 |          1 |  0.0114943 |
|       3 |         8 |         20 |   0.229885 |
|       3 |         9 |          0 |          0 |
|       3 |        10 |          1 |  0.0114943 |
+---------+-----------+------------+------------+

which can be recreated with

CREATE TABLE IF NOT EXISTS tablename ( u_id INT NOT NULL,   a_id MEDIUMINT NOT NULL,s_count MEDIUMINT NOT NULL,  weighted FLOAT NOT NULL)ENGINE=INNODB;
INSERT INTO tablename (u_id,a_id,s_count,weighted ) VALUES (1,1,17,0.0521472392638),(1,2,80,0.245398773006),(1,3,2,0.00613496932515),(1,4,1,0.00306748466258),(1,5,1,0.00306748466258),(1,6,20,0.0613496932515),(1,7,3,0.00920245398773),(1,8,100,0.306748466258),(1,9,100,0.306748466258),(1,10,2,0.00613496932515),(2,1,1,0.00327868852459),(2,2,1,0.00327868852459),(2,3,100,0.327868852459),(2,4,200,0.655737704918),(2,5,1,0.00327868852459),(2,6,1,0.00327868852459),(2,7,0,0.0),(2,8,0,0.0),(2,9,0,0.0),(2,10,1,0.00327868852459),(3,1,15,0.172413793103),(3,2,40,0.459770114943),(3,3,0,0.0),(3,4,0,0.0),(3,5,0,0.0),(3,6,10,0.114942528736),(3,7,1,0.0114942528736),(3,8,20,0.229885057471),(3,9,0,0.0),(3,10,1,0.0114942528736);

the simple version of what I want to do is

SELECT u_id, SUM(weighted) as total FROM tablename WHERE a_id IN (1,2,3,4,5,6,7,8,9) GROUP BY u_id ORDER BY total DESC;

which gives the results

+---------+-------------------+
| u_id    | total             |
+---------+-------------------+
|       2 | 0.996721301227808 |
|       1 | 0.993865059688687 |
|       3 | 0.988505747169256 |
+---------+-------------------+

the more complex version I want to do is to weight the results based on the count from a u_id, so taking the results from

query 1
SELECT count FROM tablename WHERE u_id = 1 

would return

+-----------+------------+
| a_id      | count      |
+-----------+------------+
|         1 |         17 |
|         2 |         80 |
|         3 |          2 |
|         4 |          1 |
|         5 |          1 |
|         6 |         20 |
|         7 |          3 |
|         8 |        100 |
|         9 |        100 |
|        10 |          2 |
+-----------+------------+

which would then be used to calculate the sum, should give

+---------+-------------------+
| u_id    | total             |
+---------+-------------------+
|       1 | 83.15337423       |
|       3 | 65.05747126       |
|       2 | 1.704918033       |
+---------+-------------------+

eg calculating with u_id =3 would be done by

sum(count value from query 1 * weighting value for u_id = 3 for each a_id)

17 *    0.172413793 =2.931034483
80 *    0.459770115 =36.7816092
2   *   0           =0
1   *   0           =0
1   *   0           =0
20  * 0.114942529   =2.298850575
3   * 0.011494253   =0.034482759
100 * 0.229885057   =22.98850575
100 *   0           =0
2   * 0.011494253   =0.022988506
sums up to              65.05747126 

How can I do this with a single query?

解决方案

You can do this using a subquery. The query that gets the counts for a particular id is:

SELECT a_id, s_count FROM tablename WHERE u_id = <id>

You will want to left join the result of this subquery into the main table, then sub over the appropriate multiplication, like so:

SELECT u_id, SUM(counts.s_count * tablename.weighted) AS total FROM tablename 
LEFT JOIN (SELECT a_id, s_count FROM tablename WHERE u_id = 1) counts
  ON tablename.a_id = counts.a_id
GROUP BY u_id

这篇关于MySQL 乘法子查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！