mysql - 通过应用特定过滤器来查询和分组数据

Question

我有下表(仅示例)，其中包含特定商品的 ID 及其在特定时间段内各自的生产成本(在本例中省略)

|   id | cost|
|------|-----|
| 1667 |  20 |
| 2000 |  25 |
| 2000 |  30 |
| 1667 |  35 |
| 3244 | 500 |
| 3244 |   0 |
| 3244 |  25 |
| 3244 |  26 |
| 9999 |  56 |
| 3814 | 526 |
| 9999 | 699 |
| 3814 |  13 |

我正在尝试提取每个特定 ID 的最大值，到目前为止我已经成功了。输出如下所示:

|   id |  cost |
|------|-------|
| 2000 |    30 |
| 1667 |    35 |
| 3244 |   500 |
| 3814 |   526 |
| 9999 |   699 |

我使用的代码是:

 SELECT *
 FROM t1 WHERE (id,cost) IN 
 (SELECT id, max(cost)
 FROM t1
 GROUP BY id
 )

但是，我想在获得最大值之前应用额外的过滤器，因为有时成本输入为 0 或数字太高。

• 首先，我想排除所有price=0 的ID。
• 其次，我想排除高于特定 ID 的 2x3rd 四分位数的所有价格。

例如，如果我们采用 ID=3244，则过程将如下所示:

|   id |  cost |        |   id |  cost |        |   id |  cost |
|------|-------|        |------|-------|        |------|-------|
| 3244 |    500|        | 3244 |    500|        |      |       |
| 3244 |      0|        |      |       |        |      |       |
| 3244 |     25|        | 3244 |     25|        | 3244 |     25|
| 3244 |     26|        | 3244 |     26|        | 3244 |     26|

我从 ID=3244 得到的数字是 26，我想对每个 ID 重复这个过程。

Answer 1

使用这个definition

For a set of data, a number for which 75% of the data is less than that number. The third quartile is the same as the median of the part of the data which is greater than the median. Same as 75th percentile.

<强> SqlFiddle Demo

SELECT item.id, MAX(cost) cost
 FROM item
 JOIN (
      SELECT item.id, avg(cost) thirdQ
      FROM item
      JOIN (
              SELECT id, avg(cost) mean_cost
              FROM item
              WHERE cost <> 0
              GROUP BY id
           ) T1
        ON item.id = T1.id
      WHERE cost > mean_cost and cost <> 0
      GROUP BY item.id
     ) T2
  ON item.id = T2.id
 WHERE cost < T2.thirdQ
 GROUP BY item.id

输出

|   id | cost |
|------|------|
| 1667 |   20 |
| 2000 |   25 |
| 3244 |   26 |
| 3814 |   13 |
| 9999 |   56 |