sql语句编写函数_如何使用命名结果集和函数编写模块化的可读SQL

sql语句编写函数

by Lak Lakshmanan

由Lak Lakshmanan

如何使用命名结果集和函数编写模块化的可读SQL (How to write modular, readable SQL using named result sets and functions)

My professional journey in computers has involved C++, then Java, and now Python. SQL remains, at best, a foreign language. For my own sanity, therefore, I’ve brought some of my programming best practices to SQL. In particular, the WITH statement has been my friend.

我在计算机领域的职业生涯涉及C ++，然后是Java，现在是Python。 SQL充其量仍是一门外语。 因此，出于我的理智，我将一些编程最佳实践引入了SQL。 特别是WITH语句一直是我的朋友。

I’ll use a public dataset of London bikeshares in Google BigQuery to demonstrate. Let’s say we want to find whether bikes get rented for longer durations on weekends.

我将在Google BigQuery中使用公开的伦敦单车共享数据集进行演示。 假设我们要查找自行车是否在周末租用了更长的时间。

1.常数，不是硬编码数字 (1. Constants, not hardcoded numbers)

A good first step is to define constants we will use throughout my query (See full query):

好的第一步是定义将在整个查询中使用的常量(请参阅完整查询 )：

#standardsqlWITH constants AS (  SELECT  600 AS SHORT_DUR,         1800 AS LONG_DUR,         ['Sun', 'Mon', 'Tues', 'Wed', 'Thurs', 'Fri', 'Sat'] AS daysofweek),

Here, I’m defining rides of less than 10 minutes as “short” and rides of over 30 minutes as “long.” Notice how, by defining these constants up-front, I can make it quite easy to try out different numbers. The use of named constants will also make the query a lot more readable.

在这里，我将少于10分钟的行程定义为“短”，将超过30分钟的行程定义为“长”。 注意，通过预先定义这些常量，我可以很容易地尝试不同的数字。 使用命名常量也将使查询更具可读性。

2.命名结果集 (2. Named result sets)

Another thing you want to do to increase readability is to decompose the query into named result sets. Instead of writing queries and subqueries and counting parantheses, I tend to use WITH statements a lot. Like functions in languages like C++ or Python, named result sets allow for both reuse and logical separation.

要提高可读性的另一件事是将查询分解为命名结果集。 与编写查询和子查询以及计算无用信息相比，我倾向于大量使用WITH语句。 像C ++或Python这样的语言中的函数一样，命名结果集允许重用和逻辑分离。

I first define a query to pull out the fields I want, and name this result set as bikeshare (full query):

我首先定义一个查询以提取所需的字段，并将此结果集命名为bikeshare( 完整查询 )：

bikeshare AS (  SELECT    IF(duration < SHORT_DUR, 1, 0) AS short_ride,    IF(duration > LONG_DUR,  1, 0) AS long_ride,    daysofweek[ORDINAL(EXTRACT(DAYOFWEEK FROM start_date))] AS dayofweek  FROM `bigquery-public-data.london_bicycles.cycle_hire`, constants)

Notice that the FROM clause has to include the “constants” in order to use the defined constants.

注意，FROM子句必须包含“常量”才能使用定义的常量。

3. SQL函数 (3. SQL functions)

You can decompose complex queries using the WITH keyword and create named result sets. But what about complex parsing? In the snippet above, the line pulling the day of the week and indexing into the daysofweek array is not readable, is it? And it is quite likely that this is something that you’d want in another place.

您可以使用WITH关键字分解复杂的查询并创建命名结果集。 但是复杂的解析呢？ 在上面的代码段中，无法理解拉扯星期几并索引到daysofweek数组中的线，是吗？ 而且这很可能是您想要在其他地方使用的东西。

Use a SQL function so that you can reuse this expression:

使用SQL函数，以便可以重用此表达式：

CREATE TEMPORARY FUNCTION dayOfWeek(ts TIMESTAMP,                                     days ARRAY<STRING>) AS(  days[ORDINAL(EXTRACT(DAYOFWEEK FROM ts))]);

I’m defining a function dayOfWeek that, given a timestamp and array of day names, will return the day of the week that the time in the timestamp corresponds to. Once we have this function defined, the named result set in the previous section becomes cleaner (full query):

我正在定义一个函数dayOfWeek ，给定时间戳记和日期名称数组，该函数将返回时间戳记中的时间对应的星期几。 定义此函数后，上一节中的命名结果集将变得更加整洁( 完整查询 )：

bikeshare AS (  SELECT    IF(duration < SHORT_DUR, 1, 0) AS short_ride,    IF(duration > LONG_DUR,  1, 0) AS long_ride,    dayOfWeek(start_date, daysofweek) AS dayofweek  FROM `bigquery-public-data.london_bicycles.cycle_hire`, constants)

简单性本身 (Simplicity itself)

Once we have named constants and named result sets, the final query is simplicity itself:

一旦我们命名了常量和命名结果集，最后的查询就是简单性本身：

SELECT   dayofweek,  SUM(short_ride)/COUNT(short_ride) AS frac_short_rides,  SUM(long_ride)/COUNT(long_ride)  AS frac_long_rides,  COUNT(short_ride) AS num_all_ridesFROM  bikeshareGROUP BY  dayofweekORDER BY frac_long_rides DESC

Here’s the full query, and the ensuing result:

这是完整的查询 ，以及随后的结果：

Weekdays are for quick, short commutes and weekends are for long, slow rides. Makes perfect sense!

工作日是指快速，短途通勤，而周末是指长，慢速乘车。 很有道理！

翻译自: https://www.freecodecamp.org/news/how-to-write-modular-readable-sql-using-named-result-sets-and-functions-ac49f1ca25c1/

sql语句编写函数