jeudi 29 janvier 2015

Calculations involving thresholds between two tables


I have a table comment and a table price like below. The key columns in both tables are ticker_id, price_datetime and price_open. The last column threshold in table comment (which currently NULL) is something that I need to fill in with after some calculation queries (if this is achievable).


comment table:



+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| comment_id | comment_datetime | author | comment | ticker_id | price_datetime | price_open | threshold |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| 1 | 2014-09-22 06:05:00 | A1 | C1 | 343 | 2014-09-22 08:00:00 | 53.25000 | |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| 2 | 2014-09-22 06:39:00 | A2 | C2 | 1 | 2014-09-22 08:00:00 | 62.00000 | |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| 3 | 2014-09-22 08:13:00 | A3 | C3 | 178 | 2014-09-22 08:13:00 | 5.15000 | |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+


price table:



+----------+---------------------+------------+-----------+
| price_id | price_datetime | price_open | ticker_id |
+----------+---------------------+------------+-----------+
| 1 | 2014-09-22 08:01:00 | 62.00000 | 1 |
+----------+---------------------+------------+-----------+
| 2 | 2014-09-22 08:02:00 | 62.00000 | 1 |
+----------+---------------------+------------+-----------+
| 3 | 2014-09-22 08:03:00 | 62.00000 | 1 |
+----------+---------------------+------------+-----------+


In each row of table comment, price_open will be used as "base price".




  • For each row of table comment



    • Match the ticker_id, price_datetime and price_open with table price

    • Then, get the +-2 days for each price_datetime (follows with the price_open)

    • Then, count whether any of the price.price_open within that 5 days exceeds 5%, 10% or 15% of the "base price"




  • Conditions:



    • If any of the price.price_open within that 5 days equals/exceeds 15% of the "base price", then fill in comment.threshold with "R"

    • If any of the price.price_open within that 5 days equals/exceeds 10% of the "base price" (but less than 15%), then fill in comment.threshold with "A"

    • If any of the price.price_open within that 5 days equals/exceeds 5% of the "base price" (but less than 10%), then fill in comment.threshold with "Y"

    • If any of the price.price_open within that 5 days is less 5%, then fill in comment.threshold with "C"

    • For empty values in columns comment.price_datetime and comment.price_open, we will leave it NULL as it is, thus NULL for comment.threshold as well.




Is the above going to be achievable in MySQL using JOIN? I am trying to learn about JOIN right now, unfortunately it seems way too complicated to me, I have no clue about the query that I should execute as I just started learning MySQL.


I've tried explaining my question in details, but if there's anything unclear, kindly let me know.


Any help would be much appreciated. Thank you.


EDIT (as requested by Verace):


CREATE statements:



CREATE TABLE `comment` (
`comment_id` int(11) NOT NULL AUTO_INCREMENT,
`comment_datetime` datetime NOT NULL,
`author` varchar(25) NOT NULL,
`title` varchar(250) NOT NULL,
`comment` text NOT NULL,
`ticker_id` int(11) NOT NULL,
`price_datetime` datetime DEFAULT NULL,
`price_open` decimal(12,5) DEFAULT NULL,
`threshold` varchar(10) DEFAULT NULL,
PRIMARY KEY (`comment_id`)
)

CREATE TABLE `price` (
`price_id` int(11) NOT NULL AUTO_INCREMENT,
`price_open` decimal(12,5) DEFAULT NULL,
`ticker_id` int(11) NOT NULL,
`price_datetime` datetime NOT NULL,
PRIMARY KEY (`price_id`),
UNIQUE KEY `datetime` (`price_datetime`,`ticker_id`)
)


Expected result:



+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| comment_id | comment_datetime | author | comment | ticker_id | price_datetime | price_open | threshold |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| 1 | 2014-09-22 06:05:00 | A1 | C1 | 343 | 2014-09-22 08:00:00 | 53.25000 | C |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| 2 | 2014-09-22 06:39:00 | A2 | C2 | 1 | 2014-09-22 08:00:00 | 62.00000 | Y |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
| 3 | 2014-09-22 08:13:00 | A3 | C3 | 178 | 2014-09-22 08:13:00 | 5.15000 | R |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+




Aucun commentaire:

Enregistrer un commentaire