Устали от FizzBuzz?

В последнее время было много вопросов FizzBuzz, и вот еще один! Конечно, довольно просто сделать FizzBuzz с SQL с помощью вычислений .

Итак, я собираюсь сделать FizzBuzz, который будет не только функциональным, но и продемонстрировать некоторые основные функции SQL в целом и MySQL PostgreSQL в частности. Чтобы сделать его более увлекательным, мы будем работать с 5 ключевыми словами вместо 2: Fizz, Buzz, Woof, Ping, Plop, и они будут назначены пользователям.

Будут много сценариев, поэтому просто несите меня. Обратите внимание, что я использую нотацию Pascal.

Создать исходную схему

DROP SCHEMA IF EXISTS PhrancisFizzBuzz CASCADE;
CREATE SCHEMA PhrancisFizzBuzz;
-- this table will hold the list of numbers to check for FizzBuzz
CREATE TABLE PhrancisFizzBuzz.Number(
    n INT NOT NULL
); 
-- the following tables will hold multiplier values based on table PhrancisFizzBuzz.Number
CREATE TABLE PhrancisFizzBuzz.Fizz(
    Number INT,
    Word VARCHAR(4) NOT NULL DEFAULT 'Fizz'
);
CREATE TABLE PhrancisFizzBuzz.Buzz(
    Number INT,
    Word VARCHAR(4) NOT NULL DEFAULT 'Buzz'
);
CREATE TABLE PhrancisFizzBuzz.Woof(
    Number INT,
    Word VARCHAR(4) NOT NULL DEFAULT 'Woof'
);
CREATE TABLE PhrancisFizzBuzz.Ping(
    Number INT,
    Word VARCHAR(4) NOT NULL DEFAULT 'Ping'
);
CREATE TABLE PhrancisFizzBuzz.Plop(
    Number INT,
    Word VARCHAR(4) NOT NULL DEFAULT 'Plop'
);

Заполнить таблицы FizzBuzz

Теперь я начну создавать процедуры для выполнения этой работы.

CREATE OR REPLACE FUNCTION PhrancisFizzBuzz.fncPopulateFizzBuzz(
    prmMaxNumber INT, -- for example 100
    prmFizz INT,
    prmBuzz INT,
    prmWoof INT,
    prmPing INT,
    prmPlop INT
) RETURNS VOID
AS -- begin literal string with escape $$
$$
BEGIN
SET SEARCH_PATH = PhrancisFizzBuzz;
-- initialize and populate list of initial numbers
TRUNCATE TABLE Number;
INSERT INTO Number (n)
    SELECT * FROM GENERATE_SERIES(1,  prmMaxNumber);
-- populate the FizzBuzz tables with multiples
-- based on user parameter choice
TRUNCATE TABLE Fizz; 
INSERT INTO Fizz
    SELECT n
    FROM Number
    WHERE COALESCE(prmFizz,0) <> 0 AND n % prmFizz = 0;
INSERT INTO Buzz
    SELECT n
    FROM Number
    WHERE COALESCE(prmBuzz,0) <> 0 AND n % prmBuzz = 0;
INSERT INTO Woof
    SELECT n
    FROM Number
    WHERE COALESCE(prmWoof,0) <> 0 AND n % prmWoof = 0;
INSERT INTO Ping
    SELECT n
    FROM Number
    WHERE COALESCE(prmPing,0) <> 0 AND n % prmPing = 0;
INSERT INTO Plop
    SELECT n
    FROM Number
    WHERE COALESCE(prmPlop,0) <> 0 AND n % prmPlop = 0;
END;
$$ -- end literal string
LANGUAGE plpgsql;

Для тестирования:

SET SEARCH_PATH = PhrancisFizzBuzz;
SELECT fncPopulateFizzBuzz(100,3,5,0,0,0);
SELECT * FROM Number
    LEFT JOIN Fizz ON Number.n = Fizz.Number
    LEFT JOIN Buzz ON Number.n = Buzz.Number 
ORDER BY Number.n;

Образец результата:

введите описание изображения здесь>> </p>

<pre><code>Общее время выполнения запроса: 17 мс.
Получено 100 строк.
</code></pre>

<hr>
<h1> <strong> FizzBuzz в таблицу с <code>JOIN</code> </strong> </h1>

<p> Отображает набор результатов в таблицу со строкой для каждого слова: </p>

<pre><code>СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ PhrancisFizzBuzz.fcnTableFizzBuzz (
    prmMaxNumber INT, например 100
    prmFizz INT,
    prmBuzz INT,
    prmWoof INT,
    prmPing INT,
    prmPlop INT
    ) RETURNS VOID
    В ВИДЕ
$$
НАЧАТЬ
SET SEARCH_PATH = PhrancisFizzBuzz;
- вызвать функцию заполнения с входными параметрами
PERFORM fncPopulateFizzBuzz (
    prmMaxNumber,
    prmFizz,
    prmBuzz,
    prmWoof,
    prmPing,
    prmPlop
);
DROP TABLE IF EXESTS tmpFizzBuzzResults;
CREATE TEMP TABLE tmpFizzBuzzResults AS
SELECT num.n AS Number,
    Fizz.Word AS Fizz,
    Buzz.Word AS Buzz,
    Woof.Word AS Woof,
    Ping.Word AS Ping,
    Plop.Word AS Plop
FROM Number AS num
    LEFT JOIN Fizz ON Fizz.Number = num.n
    LEFT JOIN Buzz ON Buzz.Number = num.n
    LEFT JOIN Woof ON Woof.Number = num.n
    LEFT JOIN Ping ON Ping.Number = num.n
    LEFT JOIN Plop ON Plop.Number = num.n
;
КОНЕЦ;
$$
LANGUAGE plpgsql;
</code></pre>

<p> Для тестирования: </p>

<pre><code>SET SEARCH_PATH = PhrancisFizzBuzz;
SELECT fcnTableFizzBuzz (100,3,5,7,11,13);
SELECT * FROM tmpFizzBuzzResults ORDER BY Number ASC;
</code></pre>

<p> Выходной образец: </p>

<p> <img src =

Обновление

Я запустил последнюю функцию более 100 000 номеров и выполнил ее за 3,5 секунды.

42 голоса | спросил Phrancis 23 J000000Wednesday14 2014, 04:00:31

2 ответа


30

Распространение таблиц

Схемы базы данных не должны разрабатываться таким образом, чтобы вам нужно было создать больше таблиц для размещения большего количества данных. Здесь применяется тот же принцип. Вместо отдельных таблиц Fizz, Buzz, Woof, Ping и Plop вы должны иметь одну таблицу NoiseDefs. Классический непараметрированный FizzBuzz должен быть намного проще ...

WITH NoiseDefs(multiple, noise) AS (
    VALUES (3, 'Fizz')
         , (5, 'Buzz')
), CombinedNoises AS (
    SELECT n
         , string_agg(noise, '' ORDER BY multiple) AS noise
        FROM generate_series(1, 100) AS n
            LEFT OUTER JOIN NoiseDefs
                ON n % multiple = 0
        GROUP BY n
)
SELECT n
     , coalesce(noise, CAST(n AS TEXT)) AS noiseOrNum
    FROM CombinedNoises
    ORDER BY n;

Обратите внимание на использование VALUES и string_agg() .

Предпочитает TEXT над VARCHAR(n) в PostgreSQL

В PostgreSQL существует не много смысла для использования VARCHAR(n), а не TEXT - все, что он делает, вызывает проблемы, если длина строки превышает предел. Вы также можете разрешить шум с более чем четырьмя символами.

Неправильное использование временных таблиц в функциях

То, как ваши функции взаимодействуют с временными таблицами, неочевидно. Каждый из них манипулирует временными таблицами за кулисами, а вызывающий должен знать об этом поведении, чтобы использовать функции. Другими словами, это гайки, что SELECT fcnClassicFizzBuzz(100, 3, 5, 7, 11, 13); ничего не возвращает, и вам нужно получить результаты отдельно, используя SELECT Value FROM tmpFizzBuzzResults ORDER BY Number ASC;. (Я думаю, что вы, возможно, знали об этой проблеме, когда вы сказали Я попытался работать с RETURN TABLE с помощью этой функции, но не смог заставить ее работать. )

Функция fcnClassicFizzBuzz() заполняет Fizz, Buzz, Woof, Ping code> и Plop, но не очищает после себя. Поэтому он оставляет нежелательные данные в этих таблицах как побочный эффект. Повторные вызовы fcnClassicFizzBuzz() приведут к появлению все более и более неправильного вывода. Единственным средством для пользователя является выпуск TRUNCATE Fizz; TRUNCATE Buzz; TRUNCATE Woof; TRUNCATE Ping; TRUNCATE Plop; - вы даже не предоставили функцию удобства для отмены fncPopulateFizzBuzz().

Если вы хотите, чтобы мультипликаторы были параметризованы, кажется справедливым также , чтобы их связанные шумы были также заданы пользователем.

CREATE OR REPLACE FUNCTION FlexibleFizzBuzz(
    max INTEGER,
    n1 INTEGER, noise1 TEXT,
    n2 INTEGER, noise2 TEXT,
    n3 INTEGER, noise3 TEXT,
    n4 INTEGER, noise4 TEXT,
    n5 INTEGER, noise5 TEXT
) RETURNS TABLE(n INTEGER, noiseOrNum TEXT) AS $BODY$
    BEGIN
        RETURN QUERY
        WITH NoiseDefs(multiple, noise) AS (
            VALUES (n1, noise1)
                 , (n2, noise2)
                 , (n3, noise3)
                 , (n4, noise4)
                 , (n5, noise5)
        ), CombinedNoises AS (
            SELECT num
                 , string_agg(noise, '' ORDER BY multiple) AS noise
                FROM generate_series(1, max) AS num
                    LEFT OUTER JOIN NoiseDefs
                        ON num % multiple = 0
                GROUP BY num
        )
        SELECT num
             , coalesce(noise, CAST(num AS TEXT))
            FROM CombinedNoises
            ORDER BY num;
    END;
$BODY$ LANGUAGE plpgsql;

Избегайте использования временных таблиц , если это возможно. Там меньше забот о заботе и меньше потраченных впустую операций ввода-вывода. Чтобы кэшировать результаты, позвольте вызывающему абоненту:

CREATE TEMPORARY TABLE FizzBuzzResults10000 AS
    SELECT *
        FROM FlexibleFizzBuzz(10000, 3, 'Fizz', 5, 'Buzz', 7, 'Woof', 11, 'Ping', 13, 'Plop');

Эта реализация выполняется примерно в половине случаев в качестве исходного кода.

Перегрузка функций

Было бы неплохо предоставить удобные функции, так что некоторые параметры можно опустить. Например:

CREATE OR REPLACE FUNCTION FlexibleFizzBuzz(
    max INTEGER,
    n1 INTEGER, noise1 TEXT,
    n2 INTEGER, noise2 TEXT
) RETURNS TABLE(n INTEGER, noiseOrNum TEXT) AS $BODY$
    BEGIN
    RETURN QUERY SELECT * FROM FlexibleFizzBuzz(
        max,
        n1, noise1,
        n2, noise2,
        NULL, NULL,
        NULL, NULL,
        NULL, NULL);
    END;
$BODY$ LANGUAGE plpgsql;

Я не верю, что функции PL /pgSQL могут принимать переменное количество аргументов.

Венгерская нотация

Как вы можете сделать вывод из моей реализации выше, я не поклонник префиксов, которые вы использовали, например fnc…, prm…, tmpâ € | и tmp…. Скорее, я бы использовал cte… для табличных объектов, таких как таблицы, представления и функции, возвращающие строки. Для скаляров используйте CapitalizedNames. Я рассматриваю это как преимущество, что вы можете, например, заменить временную таблицу на представление. (Имена PostgreSQL на самом деле нечувствительны к регистру, если вы не используете lowercaseNames, но этого соглашения в исходном коде должно быть достаточно.)

ответил 200_success 23 J000000Wednesday14 2014, 16:33:59
18

Я думаю, что ваши скрипты могли бы использовать немного более вертикальные пробелы; возможно, это верхняя часть, но я нахожу это:

BEGIN
SET SEARCH_PATH = PhrancisFizzBuzz;
-- call populate function with input parameters
PERFORM fncPopulateFizzBuzz(
    prmMaxNumber,
    prmFizz,
    prmBuzz,
    prmWoof,
    prmPing,
    prmPlop
);
DROP TABLE IF EXISTS tmpFizzBuzzResults;
CREATE TEMP TABLE tmpFizzBuzzResults AS
...

Было бы немного проще:

BEGIN

    SET SEARCH_PATH = PhrancisFizzBuzz;
    -- call populate function with input parameters
    PERFORM fncPopulateFizzBuzz(
        prmMaxNumber,
        prmFizz,
        prmBuzz,
        prmWoof,
        prmPing,
        prmPlop
    );

    DROP TABLE IF EXISTS tmpFizzBuzzResults;

    CREATE TEMP TABLE tmpFizzBuzzResults AS
    ...

Мне нравится отступ BEGIN...END.


Я не понимаю, это вкладка ?

SELECT    Number.n AS Number,

Вместо этого вам понадобится место:

SELECT Number.n AS Number,

Это в cteFizzBuzzConcat; у вас есть аналогичный запрос в tmpFizzBuzzResults, но с другим псевдонимом:

SELECT    num.n AS Number,

Это непоследовательно, потому что это одно и то же поле, взятое из того же источника.


Вы сохраняете много из VARCHAR(4) non-values, которые я хотел бы видеть как значения NULL. Для 100000 номеров вы храните 400000 x4 бесполезных, пустых символов; вы должны сгладить их позже, когда вы их конкатенируете.


Еще одно: я приведу здесь пресловутую Обезьяну:

  

Единственный разумный обзор для него: не используйте SQL для создания FizzBuzz

;)

ответил Mathieu Guindon 23 J000000Wednesday14 2014, 08:01:47

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132