mysql - 有条件地将Where子句添加到MySQL查询

Question

目标

获取ratings.id哪里 ratings.ticker在某个日期范围内增加 5%:
哪里 prices.price_date > ratings.date_issued
并且如果(对该股票/经纪商组合发布了另一个评级)则 prices.price_date < new_rating.date_issued否则不要设置小于日期where子句

当前设置

表:评分

| id   | ticker | broker | issue_date | close_price |
|------|--------|--------|------------|-------------|
| 22   | ABF    | HSBC   | 2017-01-23 | 2523        |
| 775  | ABF    | HSBC   | 2017-02-15 | 2527        |
| 1111 | ABF    | HSBC   | 2017-02-28 | 2619        |

表:价格

| id    | ticker | price_date | price   |
|-------|--------|------------|---------|
| 42    | ABF    | 2017-01-24 | 2533.52 |
| 80    | ABF    | 2017-01-25 | 2531.00 |
| 145   | ABF    | 2017-01-26 | 2500.00 |
| 263   | ABF    | 2017-01-27 | 2420.00 |
| 416   | ABF    | 2017-01-28 | 2440.00 |
| 585   | ABF    | 2017-01-29 | 2440.00 |
| 754   | ABF    | 2017-01-30 | 2440.00 |
| 923   | ABF    | 2017-01-31 | 2449.00 |
| 1112  | ABF    | 2017-02-01 | 2440.00 |
| 1315  | ABF    | 2017-02-02 | 2405.00 |
| 1535  | ABF    | 2017-02-03 | 2388.00 |
| 1778  | ABF    | 2017-02-04 | 2427.00 |
| 2044  | ABF    | 2017-02-05 | 2427.00 |
| 2309  | ABF    | 2017-02-06 | 2427.00 |
| 2575  | ABF    | 2017-02-07 | 2412.00 |
| 2859  | ABF    | 2017-02-08 | 2451.00 |
| 3159  | ABF    | 2017-02-09 | 2503.00 |
| 3475  | ABF    | 2017-02-10 | 2507.00 |
| 3797  | ABF    | 2017-02-11 | 2500.00 |
| 4125  | ABF    | 2017-02-12 | 2500.00 |
| 4453  | ABF    | 2017-02-13 | 2500.00 |
| 4779  | ABF    | 2017-02-14 | 2575.00 |
| 5111  | ABF    | 2017-02-15 | 2550.00 |
| 5450  | ABF    | 2017-02-16 | 2561.00 |
| 5798  | ABF    | 2017-02-17 | 2579.00 |
| 6151  | ABF    | 2017-02-18 | 2609.02 |
| 6507  | ABF    | 2017-02-19 | 2609.02 |
| 6863  | ABF    | 2017-02-20 | 2609.02 |
| 7219  | ABF    | 2017-02-21 | 2616.00 |
| 7580  | ABF    | 2017-02-22 | 2586.00 |
| 7951  | ABF    | 2017-02-23 | 2620.36 |
| 8635  | ABF    | 2017-02-24 | 2620.00 |
| 8963  | ABF    | 2017-02-25 | 2632.72 |
| 9291  | ABF    | 2017-02-28 | 2668.00 |
| 11551 | ABF    | 2017-03-08 | 2618.00 |
| 11842 | ABF    | 2017-03-09 | 2639.00 |
| 12190 | ABF    | 2017-03-10 | 2645.00 |
| 12538 | ABF    | 2017-03-13 | 2657.00 |
| 12894 | ABF    | 2017-03-14 | 2657.50 |

当前查询

SELECT 
ratings.id AS rating_id
MIN(price_date) AS first_correct,
DATEDIFF( MIN(price_date), issue_date ) AS days_lapsed
FROM
(
    SELECT
    ratings.id,
    ratings.ticker,
    broker,
    issue_date,
    close_price,
    price_date,
    ( ( ( price - close_price) / close_price ) * 100 ) AS diff_percent,
    FROM ratings
    JOIN prices
    ON ratings.ticker = prices.ticker
    WHERE price_date > issue_date
    HAVING diff_percent >= 5
) correct_ratings
GROUP BY ratings.id

结果

| rating_id | ticker | broker | issue_date | close_price | price_date | diff_percent | first_correct | days_lapsed |
|-----------|--------|--------|------------|-------------|------------|--------------|---------------|-------------|
| 22        | ABF    | HSBC   | 2017-01-23 | 2523        | 2017-02-28 | 5.747126     | 2017-02-28    | 36          |
| 775       | ABF    | HSBC   | 2017-02-15 | 2527        | 2017-02-28 | 5.579739     | 2017-02-28    | 13          |

问题

如您所见 first_correct rating_id=22 的日期在issue_date之后的rating_id=775 。所以不应该出现在表中。仅rating_id=775应该在结果中。

Answer 1

正如我的问题中提到的，问题是我只有一个开始日期来检查百分比增长，而我实际上需要一个 start_date 和 结束日期。

得出此结果的唯一方法是检查该经纪商/股票组合的下一个评级并将其添加到名为 end_date 的新列中。

我能够使用此处找到的查询创建新列:https://stackoverflow.com/a/15247545/1281101

有时，如果评级未被取代，end_date 将为 NULL，这可以通过 IFNULL(column, return )MySQL 函数。

最终查询如下所示:

SELECT * 
FROM ( 
    SELECT rating_id, wp_broker_ratings.ticker, broker, date_issued AS start_date, issue_date_close_price, ( 
         SELECT MIN(st.date_issued) 
         FROM wp_broker_ratings st 
         WHERE st.broker = wp_broker_ratings.broker 
           AND st.ticker = wp_broker_ratings.ticker 
           AND st.date_issued > wp_broker_ratings.date_issued ) AS end_date, price_id, price_date, day_high AS absolute_correct_price, ( ( ( day_high - issue_date_close_price) / issue_date_close_price ) * 100 ) AS pct_diff 
FROM wp_broker_ratings 
INNER JOIN wp_share_prices 
ON wp_broker_ratings.ticker = wp_share_prices.ticker 
WHERE buy_sell = 'buy' 
HAVING price_date > start_date 
AND price_date <= IFNULL(end_date,'2050-01-01') 
AND pct_diff >= 5 ) correct 
GROUP BY rating_id

希望这可以帮助其他人。