如何从概念上设计查询?

(If this is in the wrong location, please let me know and I'll move it, it is programming related, but I'm looking for a general framework.)

(如果这是在错误的位置，请让我知道，我将移动它，它是编程相关的，但我正在寻找一个通用的框架。)

I'm looking to establish best practices for my query design by establishing a framework, or a design process I can follow when coming up with a new query.

我希望通过建立一个框架来为我的查询设计建立最佳实践，或者当我提出一个新的查询时可以遵循的设计过程。

I find myself developing queries and then getting confused about how to get the results I need.

我发现自己在开发查询，然后困惑于如何得到我需要的结果。

Do you have a design process or guidelines when you need to create a new query?
当您需要创建一个新的查询时，您是否有一个设计过程或指导方针?
Do you draw it out on paper?
你把它写在纸上吗?
Are there certain questions you ask yourself when coming up with a design?
在设计时，你会问自己一些问题吗?

Thank you for the help.

谢谢你的帮助。

1 个解决方案

#1

Like with any type of programming your code should be DRY, so design out the repeating parts and use stored procedures, views and functions to do these parts.

与任何类型的编程一样，您的代码应该是枯燥的，所以设计重复的部分，并使用存储过程、视图和函数来完成这些部分。

Also important in SQL is readability - at some point some poor sod (maybe a future you) is going to have to read your code and try and make sense of it. So try and avoid long queries, break them down using temp tables or views.

SQL中同样重要的是可读性——在某种程度上，一些糟糕的sod(也许是你的未来)将不得不阅读你的代码并尝试去理解它。因此，尽量避免长查询，使用临时表或视图将它们分解。

Just a few quick thoughts...

仅仅是一些简单的想法……

Design Process Questions

设计过程的问题

What tables do I need to query?
我需要查询哪些表?
What indexes and relationships do these tables have? - this may inform how you query that table
这些表有哪些索引和关系?-这可能会告诉您如何查询该表
What output am I expecting - what columns, how much data, what types?
我期望的输出是什么——什么列，多少数据，什么类型?
How frequently is the querying going to run?
查询将运行多久?

Design

设计

Map out your design on paper or a diagram, this is especially useful if you are working on a data warehouse with a star schema

在纸上或图上绘制您的设计，这对于使用星型模式处理数据仓库尤其有用

Show what tables will be used and relationships between them.

显示哪些表将被使用，以及它们之间的关系。

Comments

Add something like this to the head of any stored procedure or function, or saved SQL code, and update it when you make changes to the code. This help you and others understand what the code is for and why changes have been made.

向任何存储过程或函数或已保存的SQL代码的头部添加类似的内容，并在修改代码时更新它。这可以帮助您和其他人了解代码的用途以及更改的原因。

-----------------------------------------------------------------------------------------
-- Stored Procedure : usp_SSRS_ReportName                                               --
-- DateTime  : 07/07/2014                                                              --
-- Author    : John Smith                                                          --
-- Purpose   : Get Date for Report                              --
-----------------------------------------------------------------------------------------
-- Ver  |    Date    |  Author    | Description                                        --
-----------------------------------------------------------------------------------------
-- 1.00 | 07/07/2014 | J Smith | Created.                                          --
-- 1.10 | 10/02/2015 | B Builder | Split sales by currency, removed company param 
--                                                      --

#1