SQL view for time series of days

Question

So dealing with a rolling set of limitations of tooling & display libraries.

I have the attached query that works. It is taking a transaction table and generating a time series output counting transaction type and summing amounts by day so a graph can be displayed using a library.

The problem is that the library's embedded SQL client has limitations. So it needs to be a stored MySQL/MariaDB view that the library can access. Only problem is that an error is being thrown when attempting to save the view due to a limitation of MySQL/MariaDB.

#1351 - View's SELECT contains a variable or parameter

I'm assuming that the path forward is to stuff the offending var/parm into a Custom Function or splitting the query into multiple views to deal with the subqueries outside of any limitations. Ultimately it needs to be a single call-able view/query so the library can handle it.

I will freely admit that I'm really rusty on Custom Functions since I was never a big fan of them. I've got way too many SELECTS to nail down which [one/ones] [is/are] causing the database infarction, so I could use some thoughts/assistance.

    SELECT DATE(cal.date) AS Date, SUM(`amount`) as Total,    
          (SELECT COUNT(a.status)
            FROM `transactions`
            WHERE DATE(created_at) = DATE(cal.date)
              AND txn_type = 'payment'
              AND status = 'pending') as p,
          (SELECT COUNT(a.status)
            FROM `transactions`
            WHERE DATE(created_at) = DATE(cal.date)
              AND txn_type = 'payment'
              AND status = 'failed') as f,
          (SELECT COUNT(a.status)
            FROM `transactions`
            WHERE DATE(created_at) = DATE(cal.date)
              AND txn_type = 'payment'
              AND status = 'complete') as c,
          (SELECT COUNT(a.status)
            FROM `transactions`
            WHERE DATE(created_at) = DATE(cal.date)
              AND txn_type = 'payment'
              AND status = 'refunded') as r
FROM ( SELECT SUBDATE(NOW(), INTERVAL 90 DAY) + INTERVAL xc DAY AS date 
      FROM ( SELECT @xi:=@xi+1 as xc from ( 
           SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1, 
          (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2, 
          (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
          (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4, 
          (SELECT @xi:=-1) xc0 ) 
      xxc1 ) 
cal 
LEFT JOIN `transactions` a ON DATE(a.`created_at`) = DATE(cal.date) 
WHERE cal.date <= NOW() 
GROUP BY DATE(cal.date) 
ORDER BY cal.date DESC

user_205608 · Answer

Your variable-in-view issue comes up in your generation of the sequence of numbers. There's a better way to do that.

Create yourself this view. It gives a sequence of numbers from 0 to 124.

CREATE OR REPLACE VIEW seq_0_to_124 AS 
SELECT A.N + 5*(B.N + 5*(C.N)) AS seq
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C;

If you're using the MariaDB fork of MySQL, you don't have to create this view: it has a sequence storage engine, and you can use built-in virtual tables like seq_0_to_90.

Then you can make another cal view to get the days from 90 days ago until now.

CREATE OR REPLACE VIEW cal AS 
 SELECT (CURDATE() - INTERVAL 90 DAY + INTERVAL seq_0_to_124.seq DAY)  `date`
   FROM seq_0_to_124
  WHERE seq_0_to_124.seq <= 90;

Check this out in a fiddle here.

Finally, you can use the cal view in your query.

SELECT whatever
FROM cal 
LEFT JOIN `transactions` a   ON a.`created_at` =  cal.date 
WHERE cal.date <= NOW() 
GROUP BY cal.date 
ORDER BY cal.date DESC

Of course, you can also embed these in your query as subqueries if you prefer.

SQL view for time series of days

1 Answers