sql - 测试 SQL 查询的最佳方法

Question

关闭。这个问题是 opinion-based 。目前不接受答案。

---

想要改进这个问题吗？更新问题，以便 editing this post 可以用事实和引文来回答它。 .

已关闭 7 年前。

Improve this question

我遇到了一个问题，复杂的 SQL 查询总是出错。从本质上讲，这会导致向错误的客户发送邮件以及其他类似的“问题”。

每个人对于创建这样的 SQL 查询都有什么经验？我们每隔一周就会创建新的数据群。

以下是我的一些想法及其局限性:

• 创建测试数据虽然这可以证明我们拥有所有正确的数据，但它并不能强制排除生产中的异常情况。这些数据在今天被认为是错误的，但在 10 年前可能是正确的；它没有记录在案，因此我们只有在提取数据后才知道它。

• 创建维恩图和数据映射 这似乎是测试查询设计的可靠方法，但它并不能保证实现是正确的。它让开发人员提前计划并在编写时思考正在发生的事情。

感谢您对我的问题提供的任何意见。

Answer 1

您不会编写具有 200 行长函数的应用程序。您可以将这些长函数分解为较小的函数，每个函数都有一个明确定义的职责。

为什么要这样编写 SQL？

分解您的查询，就像分解您的函数一样。这使得它们更短、更简单、更容易理解、更容易测试、更容易重构。它允许您在它们之间添加“垫片”，并在它们周围添加“包装器”，就像在过程代码中一样。

你是如何做到这一点的？通过将查询所做的每件重要事情都放入 View 中。然后，您可以从这些更简单的 View 中组合出更复杂的查询，就像您从更原始的函数中组合出更复杂的函数一样。

最棒的是，对于大多数 View 组合，您将从 RDBMS 中获得完全相同的性能。 (对于某些人来说，您不会；那又怎样？过早的优化是万恶之源。首先正确编写代码，然后在需要时进行优化。)

Here's an example of using several view to decompose a complicated query.

在示例中，由于每个 View 只添加一个转换，因此每个 View 都可以独立测试以发现错误，并且测试很简单。

以下是示例中的基表:

create table month_value( 
    eid int not null, month int, year int,  value int );

这个表是有缺陷的，因为它使用两列，月份和年份，来表示一个数据，一个绝对月份。以下是我们对新计算列的规范:

We'll do that as a linear transform, such that it sorts the same as (year, month), and such that for any (year, month) tuple there is one and only value, and all values are consecutive:

create view cm_absolute_month as 
select *, year * 12 + month as absolute_month from month_value;

现在我们必须测试的是我们规范中固有的，即对于任何元组(年、月)，都有且只有一个(absolute_month)，并且(absolute_month)是连续的。让我们编写一些测试。

我们的测试将是一个 SQL select 查询，具有以下结构:测试名称和连接在一起的 case 语句。测试名称只是一个任意字符串。 case 语句只是 case when 测试语句 then 'passed' else 'failed' end。

测试语句只是 SQL 选择(子查询)，测试必须为 true 才能通过。

这是我们的第一个测试:

--a select statement that catenates the test name and the case statement
select concat( 
-- the test name
'For every (year, month) there is one and only one (absolute_month): ', 
-- the case statement
   case when 
-- one or more subqueries
-- in this case, an expected value and an actual value 
-- that must be equal for the test to pass
  ( select count(distinct year, month) from month_value) 
  --expected value,
  = ( select count(distinct absolute_month) from cm_absolute_month)  
  -- actual value
  -- the then and else branches of the case statement
  then 'passed' else 'failed' end
  -- close the concat function and terminate the query 
  ); 
  -- test result.

运行该查询会产生以下结果:对于每个(年，月)都有一个且只有一个(absolute_month):通过

只要month_value中有足够的测试数据，该测试就有效。

我们也可以添加一个测试以获得足够的测试数据:

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

现在让我们测试一下它的连续性:

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

现在，让我们将测试(只是查询)放入一个文件中，然后针对数据库运行该脚本。事实上，如果我们将 View 定义存储在一个脚本(或多个脚本，我建议每个相关 View 一个文件)中以针对数据库运行，我们可以将每个 View 的测试添加到相同的脚本中，这样(重新)创建 View 的行为也会运行 View 的测试。这样，当我们重新创建 View 时，我们都会得到回归测试，并且当 View 创建针对生产环境运行时， View 也将在生产环境中进行测试。