0

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     |
+------------+---------------------+--------+---------+-----------+---------------------+------------+-----------+
  • Please give us SHOW CREATE TABLE statements for your tables. Could you also give us a table with your desired results - I know you've explained it, but sometimes a table is worth a thousand words :-) – Vérace Jan 29 '15 at 0:25
  • @Vérace gimme a second and I will update my table. Thank you! – Shyuan Jan 29 '15 at 0:26
  • @Vérace I've updated my question. Thank you. :) – Shyuan Jan 29 '15 at 0:35
  • Some DML would also be cool - INSERT INTO Table blah VALUES(...). Do a quick mysqldump - - or use PasteBin if too much data. – Vérace Jan 29 '15 at 0:38
  • @Vérace I see what you mean, sorry about that. I will do a pastebin now. – Shyuan Jan 29 '15 at 0:41
0

SQL is not the be-all and end-all of programming languages. I would not try to do this in SQL, rather do it in some other language (Perl, PHP, Java, VB, etc).

A minor nit on the indexes of price. Is price_id ever used? Consider DROPping it and turning the UNIQUE KEY into the PRIMARY KEY.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.