3

The relevant tables and attempted solution have been listed at the bottom of this post. The goal and the problem are described immediately below.

The Goal

The goal of this query is to build the PHP array of objects (returned rows) below. This is a timestamped history of every transaction made for each currency pair filtered by user. I am able to achieve everything except for the cumulative_quantity values

'BTC-USD' => array(
  array(
    'timestamp' => (int),
    'price' => (float),
    'price_usd' => (float),
    'buy_sell' => (bool),
    'quantity' => (float),
    'total_value' => (float),
    'total_value_usd' => (float),
    'cumulative_quantity' => (float),
    'cumulative_quantity_base' => (float)
  )
)

The Problem

Calculating the cumulative quantity for each user's row in pf_items requires also checking whether the buy_sell field is 0 (sell) or 1 (buy) and adding/subtracting accordingly.

It is made slightly more complex as cumulative_quantity_base requires that the quantity values for BTC-USD and (for instance) BTC-EUR are merged under a simple BTC array of objects. For instance, if a row in pf_items relating to BTC-USD (base_currency and quote_currency) had a buy quantity of 5, and another row relating to BTC-EUR had a buy quantity of 7, it would need to find the cumulative value for BTC of these two rows: 12.

pf_currencies

CREATE TABLE `pf_currencies` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `symbol` varchar(255) NOT NULL
);

pf_currencies_history

CREATE TABLE `pf_currencies_history` (
  `id` int(11) NOT NULL,
  `base_currency` int(11) NOT NULL,
  `quote_currency` int(11) NOT NULL,
  `price` float NOT NULL,
  `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

pf_portfolios

CREATE TABLE `pf_portfolios` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

pf_items

CREATE TABLE `pf_items` (
  `id` int(11),
  `portfolio_id` int(11),
  `action_date` date,
  `price` float,
  `price_usd` float,
  `buy_sell` tinyint(1),
  `quantity` float,
  `base_currency` int(11),
  `quote_currency` int(11),
  `date_added` datetime
)

My Query This query achieves what is needed except for cumulative_quantity and cumulative_quantity_base as described.

SELECT (SELECT
         symbol
       FROM pf_currencies
       WHERE a.base_currency = id)
       AS base_currency,
       (SELECT
         symbol
       FROM pf_currencies
       WHERE a.quote_currency = id)
       AS quote_currency,
       a.price,
       UNIX_TIMESTAMP(a.date_time) AS unix_time
FROM pf_currencies_history a
INNER JOIN pf_items i
  ON i.portfolio_id = (SELECT
    id
  FROM pf_portfolios
  WHERE user_id = ".$userId.")
  AND a.base_currency = i.base_currency
  AND a.quote_currency = i.quote_currency
ORDER BY unix_time DESC
Nick
  • 876
  • 1
  • 14
  • 35
  • STR_TO_DATE(a.date_time, '%Y-%m-%d %H:%i:%s') - erm, what does this do? – Strawberry Jul 12 '18 at 09:23
  • Converts my date string YYYY-MM-DD 00:00:00 into a unix timestamp – Nick Jul 12 '18 at 09:28
  • 1
    No. UNIX_TIMESTAMP does that. – Strawberry Jul 12 '18 at 09:30
  • Yeah, I agree with @Strawberry, that conversion is totally unnecessary. – Shadow Jul 12 '18 at 10:12
  • Thanks, have edited and updated the query above. – Nick Jul 12 '18 at 10:25
  • The cumulative quantity is simple, there are dozens of questions around how to calculate running totals / cumulative sums with conditions here on SO. You really should search and make an honest attempt. However, I do not get the cumulative quantity base definition. Can you pls illustrate with sample data and expected output what you want to achieve there? – Shadow Jul 12 '18 at 10:27
  • As I wrote, that conversion is totally unnecessary. The `date_time` field has datetime data time, not string, so there is no need to convert it from string to date and then to unix datetime. – Shadow Jul 12 '18 at 10:28
  • @Shadow running totals are fine, but it also needs to do a statement based around the (bool) `buy_sell`. I appreciate the suggestion to Google the problem, however that is a route I have already exhausted. Any help would be appreciated and I will update the question to include more info around base. – Nick Jul 12 '18 at 10:30
  • As I wrote, you need to check questions discussing cumulative sums with conditions, such as: https://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql – Shadow Jul 12 '18 at 10:42
  • A question - your inner join between pf_items and pf_currencies_history depends on not having two records in pf_currencies_history with the same base_currency and quote_currency (and also on not having problems with comparing integers to strings). Is that intentional? – Koen Jul 12 '18 at 10:47
  • @Koen `base_currency` and `quote_currency` should be int (11) - my mistake. The inner join is on portfolio_id, purely so I can get all the history from that user's portfolio, the join is intentional. I may be misunderstanding the point you're getting at though. – Nick Jul 12 '18 at 10:54

0 Answers0