天数时间序列的 SQL 视图-解网

问：

因此，要处理工具和显示库的一系列滚动限制。

我有有效的附加查询。它采用一个交易表并生成一个时间序列输出，计算交易类型并按天计算金额，以便可以使用库显示图表。

问题在于该库的嵌入式 SQL 客户端存在局限性。因此，它需要是库可以访问的存储 MySQL/MariaDB 视图。唯一的问题是，由于MySQL / MariaDB的限制，在尝试保存视图时会抛出错误。

#1351 - View 的 SELECT 包含变量或参数

我假设前进的道路是将有问题的 var/parm 塞进自定义函数中，或者将查询拆分为多个视图以处理超出任何限制的子查询。最终，它需要是一个可调用的视图/查询，以便库可以处理它。

我会坦率地承认，我对自定义函数真的很生疏，因为我从来都不是它们的忠实粉丝。我有太多的 SELECTS 来确定哪个 [one/ones] [是/正在] 导致数据库梗塞，所以我可以使用一些想法/帮助。

    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

MySQL SQL 日期时间序列序列

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;

如果您使用的是 MySQL 的 MariaDB 分支，则不必创建此视图：它有一个序列存储引擎，并且您可以使用内置的虚拟表，例如 .seq_0_to_90

然后，您可以创建另一个视图来获取从 90 天前到现在的天数。cal

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;

在这里用小提琴检查一下。

最后，您可以在查询中使用该视图。cal

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

当然，如果您愿意，也可以将它们作为子查询嵌入到查询中。

天数时间序列的 SQL 视图

SQL view for time series of days

评论

评论