mysql - mysql 在另一个流行值查询的结果中找到一个流行值

Question

我有一张完整的股票和期权头寸表（“期权头寸”是期权到期日和行使价的组合）。列：

userID - the person who owns the position
stock_symbol - the stock they own
call_expiration - expiration date for a call option they've sold against the stock
call_strike - strike price for the same call option

用户可以为同一个 stock_symbol 拥有多个股票头寸，如果他们这样做了，那么他们可能有针对同一符号的不同看涨期权。例如，用户 399 在 AAPL 中有 3 个职位，每个职位都有不同的看涨期权：

+---------+--------------+-----------------+-------------+
| user_id | stock_symbol | call_expiration | call_strike |
+---------+--------------+-----------------+-------------+
|     399 | AAPL         | 2018-07-20      |         185 |
|     399 | AAPL         | 2018-07-20      |         190 |
|     399 | AAPL         | 2018-08-17      |         180 |
|     399 | X            | 2018-07-20      |          13 |
|     802 | AAPL         | 2018-07-20      |         190 |
|     802 | X            | 2019-01-18      |          14 |
|     802 | MU           | 2018-07-20      |          38 |
|     802 | MIC          | 2018-07-20      |        42.5 |
|     802 | AAPL         | 2018-07-20      |         190 |
|    1079 | MU           | 2018-08-17      |          39 |
|    1079 | X            | 2018-07-20      |          14 |

我想知道最常持有哪些股票，但我希望每个用户有 1 票（在上面的数据中，X 符号获胜，因为 3 个用户在其中有一个仓位；AAPL 有更多仓位，但它们只有 2 个用户拥有） . 因此，如果同一个用户在同一个 stock_symbol 中有多个职位，那么它只计为对该 stock_symbol 的 1 票。如果频率相同，则按符号字母顺序排列。也许有一种更简单的方法，但这有效：

SELECT stock_symbol, COUNT(*) AS symbol_count FROM 
(SELECT DISTINCT(user_id), stock_symbol FROM user_positions) AS temp
GROUP BY stock_symbol 
ORDER BY symbol_count DESC, stock_symbol ASC LIMIT 10;

然后我想知道每个流行符号最常见的到期日期。以下是我如何为硬编码的 stock_symbol (AAPL) 找到它。如果出现平局，则最早的到期日期获胜：

SELECT call_expiration, COUNT(*) AS exp_count FROM positions
WHERE stock_symbol='AAPL' 
GROUP BY call_expiration 
ORDER BY exp_count DESC, call_expiration ASC;

最后，我想知道给定交易品种和到期日最常见的罢工价格。这是我如何找到硬编码符号和到期日期的方法。如果出现平局，则最低行使价获胜：

SELECT call_strike, COUNT(*) AS strike_count FROM positions
WHERE stock_symbol='AAPL' AND call_expiration = '2018-07-20'
GROUP BY call_strike
ORDER BY strike_count DESC, call_strike ASC;

这些工作，我可以将这些调用放入一个 php 脚本并循环遍历第一个查询的结果（生成 10 个最常用的 stock_symbols），但我想知道是否有办法让 mysql 完成所有这些我。

最终目标是这样的表格：

popular position #1: S1   E1   K1
popular position #2: S2   E2   K2
popular position #3: S3   E3   K3

在哪里：

S1 = the most commonly held stock_symbol when each user gets 1 vote
E1 = the most common expiration date across all positions for symbol S1 (if a user has multiple positions for that symbol then you can consider all of them)
K1 = the most common call_strike for all positions where the symbol and expiraiton date are fixed at S1 and E1. (if a user has multiple positions for that symbol then you can consider all of them)

我不确定这是否是一个坏主意，但我想在我的查询中创建连接 call_expiration+call_strike 的中间列，或者可能所有 3 个：symbol+expiration+strike; 不太清楚，因为在第一种情况下（最受欢迎的符号）我希望用户每个人只有 1 票，但在接下来的 2 个查询中（最受欢迎的到期日期和该到期日期的最受欢迎的行使价）没关系考虑用户的所有头寸，即使他们有超过 1 个相同的交易品种。在单个查询中可能有太多事情要做？

--- 2018 年 6 月 24 日在下面添加了示例数据 ---

CREATE TABLE test_positions (
position_id int unsigned auto_increment,
user_id int unsigned,
stock_symbol varchar(10),
call_expiration date,
call_strike float,
primary key (position_id)
);

INSERT INTO test_positions 
 (user_id, stock_symbol, call_expiration, call_strike)
VALUES 
 (399,  'AAPL', '2018-07-20', 185),
 (399,  'AAPL', '2018-07-20', 190),
 (399,  'AAPL', '2018-08-17', 190),
 (399,  'AAPL', '2018-09-21', 180),
 (399,  'X',    '2018-07-20', 35),
 (802,  'X',    '2018-07-20', 35.5),
 (1079, 'X',    '2018-07-20', 35),
 (1079, 'X',    '2018-07-20', 35.5),
 (1079, 'AAPL', '2018-07-20', 190),
 (1079, 'AAPL', '2018-07-20', 185),
 (1079, 'MRK',  '2018-07-20', 62.5),
 (1079, 'MRK',  '2018-08-17', 60);

使用此数据的所需查询结果是：

+--------------+-----------------+-------------+
| stock_symbol | call_expiration | call_strike |
+--------------+-----------------+-------------+
| X            | 2018-07-20      |          35 |
| AAPL         | 2018-07-20      |         190 |
| MRK          | 2018-07-20      |        62.5 |
+--------------+-----------------+-------------+

因为符号 X 由 3 个用户持有。尽管 AAPL 有更多职位，但只有 2 个独立用户在 AAPL 中有职位，所以我们会说 X 比 AAPL 更受欢迎。

在符号 X 的 4 个位置中，最常见的 call_expiration 是 2018-07-20，如果我们查看所有符号为 X 且 call_expiration 为 2018-07-20 的 call_strikes，那么它是平局（2 有 35 和 2有 35.5) 所以我们要选择 2 中的较低者。

在 MRK 的 2 个位置中，call_expiration 平局（2 个日期各 1 个）所以我们选择 2 个中较早的一个（2018-07-20），然后如果您查看符号 MRK 的所有位置，其中call_expiration 是 2018-07-20 最常见的（好吧，唯一的）call_strike 是 62.50。

标签： mysql