您不会编写具有 200 行长函数的应用程序。您可以将这些长函数分解为较小的函数,每个函数都有一个明确定义的职责。
为什么要这样写 SQL?
分解您的查询,就像你分解你的功能一样。这使得它们更短、更简单、更容易理解,更容易测试,更容易重构。它允许您在它们之间添加“垫片”,并在它们周围添加“包装器”,就像在过程代码中一样。
你怎么做到这一点?通过将查询所做的每件重要事情都放入视图中。然后你compose从这些更简单的视图中创建更复杂的查询,就像从更原始的函数中组合出更复杂的函数一样。
最棒的是,因为most视图的组合,您将从 RDBMS 中获得完全相同的性能。 (对于有些人来说,你不会;那又怎样?过早的优化是万恶之源。首先正确编码,then如果需要的话进行优化。)
下面是使用多个视图来分解复杂查询的示例。 https://stackoverflow.com/questions/702500/sql-query-to-collapse-duplicate-values-by-date-range/712309#712309
在示例中,由于每个视图仅添加一个转换,因此每个视图都可以独立测试以发现错误,并且测试简单。
这是示例中的基表:
create table month_value(
eid int not null, month int, year int, value int );
该表是有缺陷的,因为它使用两列(月份和年份)来表示一个数据,即绝对月份。这是我们对新计算列的规范:
我们将其作为线性变换来实现,这样它的排序与(年,月)相同,并且对于任何(年,月)元组都有一个且唯一的值,并且所有值都是连续的:
create view cm_absolute_month as
select *, year * 12 + month as absolute_month from month_value;
现在我们必须测试的是我们规范中固有的,即对于任何元组(年,月),都有一个且只有一个(absolute_month),并且(absolute_month)是连续的。让我们写一些测试。
我们的测试将是 SQLselect
查询,具有以下结构:测试名称和案例语句连接在一起。测试名称只是一个任意字符串。案例陈述只是case when
测试语句then 'passed' else 'failed' end
.
测试语句只是 SQL 选择(子查询),测试必须为真才能通过。
这是我们的第一个测试:
--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.
运行该查询会产生以下结果:For every (year, month) there is one and only one (absolute_month): passed
只要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 );
现在,让我们将测试(只是查询)放入一个文件中,然后针对数据库运行该脚本。事实上,如果我们将视图定义存储在一个脚本(或多个脚本,我建议每个相关视图一个文件)中以针对数据库运行,我们可以将每个视图的测试添加到same脚本,以便(重新)创建视图的行为也运行视图的测试。这样,当我们重新创建视图时,我们都会得到回归测试,并且当视图创建针对生产运行时,视图也将在生产中进行测试。