SQL: изучение того, как использовать функции процентиля в SQL

Вот пример использования некоторых функций для получения эквивалента вашего кода R. Он использует percentile_cont для получения двух граничных значений между квантилями, которые затем можно использовать в выражениях case для получения меток роста или возраста. «Перекрестное соединение» просто добавляет эти значения в каждую строку таблицы для простоты использования в выражениях case. (Это показано далее в упомянутой скрипте ниже.)

SELECT
      t.country
    , t.gender
    , CASE WHEN t.height <= height_quantiles.q1 THEN 'short' 
           WHEN t.height <= height_quantiles.q2 THEN 'medium' 
           ELSE 'tall' 
      END AS height_group
    , CASE WHEN t.age <= age_quantiles.q1 THEN 'young' 
           WHEN t.age <= age_quantiles.q2 THEN 'middle_age' 
           ELSE 'old' 
      END AS age_group
    , AVG(CASE WHEN t.owns_bicycle = 'Yes' THEN 1 ELSE 0 END) AS bicycle_proportion
    , COUNT(*) AS counts
FROM MY_TABLE t
CROSS JOIN (
    SELECT
          PERCENTILE_CONT(0.33) WITHIN GROUP (ORDER BY height) AS q1
        , PERCENTILE_CONT(0.67) WITHIN GROUP (ORDER BY height) AS q2
    FROM MY_TABLE t
    ) height_quantiles
CROSS JOIN (
    SELECT
          PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY age) AS q1
        , PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY age) AS q2
    FROM MY_TABLE t
    ) age_quantiles
GROUP BY
      t.country
    , t.gender
    , height_group
    , age_group
ORDER BY
      t.country
    , t.gender
    , height_group
    , age_group

рабочий пример

Re: "адаптируемый" вариант. SQL не является языком программирования, он имеет дело с наборами данных, поэтому имитация кода R может быть возможна, если вы используете «процедурное расширение» для SQL (например, plsql в Postgres), но поскольку я не могу ничего запустить в Netazza лучшее, что я могу предложить, это что-то в этом роде.

with CTE as (
    SELECT
      *
     , NTILE(4) OVER (ORDER BY height) AS height_quantile
     , NTILE(5) OVER (ORDER BY age) AS age_quantile
    FROM my_table
 )
, height_labels as (
  select
       height_quantile hq
     , concat(floor(min(round(height::decimal,2))) , ' to '
            , max(ceiling(round(height::decimal,2)) ) ) height_label
  from CTE
 group by
       height_quantile
  )
, age_labels as (
  select
       age_quantile aq
     , concat(min(age ) , ' to '
            , max(age ) ) age_label
  from CTE
 group by
       age_quantile
  )
select
  *
from CTE 
inner join height_labels h on cte.height_quantile=h.hq
inner join age_labels    a on cte.age_quantile=a.aq
order by
    height, age

Третья рабочий пример

В этом подходе метки генерируются из пар минимум/максимум каждой NTILE, а затем из тех, которые используются в окончательном выводе. Для упрощения создания этикетки я ввел функции пол и потолок.

Вот простой пример

CREATE TABLE students (
  id INT,
  name VARCHAR(255),
  marks INT
);

INSERT INTO students VALUES
(1, 'John', 90),
(2, 'Jane', 80),
(3, 'Mike', 70),
(4, 'Peter', 60);

SELECT
  id,
  name,
  marks,
  PERCENTILE_CONT(marks, 0.25) AS 25th_percentile,
  PERCENTILE_CONT(marks, 0.50) AS 50th_percentile,
  PERCENTILE_CONT(marks, 0.75) AS 75th_percentile
FROM students;

ОП здесь - вот моя собственная попытка решить мою проблему.

Я думал, что могу сделать это в 3 шага:

• Шаг 1: Сначала рассчитайте необходимые категории процентилей (например, 3 группы, 5 групп) на основе роста и возраста.
• Шаг 2: Затем замените значения роста и возраста в исходной таблице этими категориями.
• Шаг 3: Наконец, создайте легенду/справочную таблицу, содержащую минимальные/максимальные значения для каждой категории.

Я не уверен, что это правильно (хотя это выглядит правильно?).

Может ли кто-нибудь высказать свое мнение по этому поводу?

CREATE TABLE MY_TABLE (
    country VARCHAR(50),
    gender CHAR(1),
    age INTEGER,
    height FLOAT,
    owns_bicycle VARCHAR(3)
);

INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'M', 25, 175.99, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 63, 163.65, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 83, 166.01, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'M', 94, 178.92, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'M', 63, 173.24, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'M', 87, 156.27, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 89, 159.26, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'M', 61, 179.14, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 49, 167.55, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'F', 53, 172.82, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 62, 161.18, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 31, 173.08, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'F', 33, 166.13, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 50, 177.42, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 57, 155.56, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'M', 57, 158.47, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 27, 152.85, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 89, 156.31, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'F', 99, 179.31, 'No');

CREATE TABLE height_groups AS
SELECT
  country,
  gender,
  NTILE(3) OVER (ORDER BY height) AS height_group,
  MIN(height) AS min_height,
  MAX(height) AS max_height
FROM MY_TABLE
GROUP BY country, gender, height;

CREATE TABLE age_groups AS
SELECT
  country,
  gender,
  NTILE(5) OVER (ORDER BY age) AS age_group,
  MIN(age) AS min_age,
  MAX(age) AS max_age
FROM MY_TABLE
GROUP BY country, gender, age;

CREATE TABLE final AS
SELECT
  h.country,
  h.gender,
  h.height_group,
  a.age_group,
  AVG(CASE WHEN t.owns_bicycle = 'Yes' THEN 1 ELSE 0 END) AS bicycle_proportion,
  COUNT(*) AS counts
FROM height_groups h
JOIN age_groups a ON h.country = a.country AND h.gender = a.gender
JOIN MY_TABLE t ON h.country = t.country AND h.gender = t.gender AND t.height BETWEEN h.min_height AND h.max_height AND t.age BETWEEN a.min_age AND a.max_age
GROUP BY h.country, h.gender, h.height_group, a.age_group;


CREATE TABLE lookup AS
SELECT
  h.country,
  h.gender,
  h.height_group,
  a.age_group,
  h.min_height,
  h.max_height,
  a.min_age,
  a.max_age
FROM height_groups h
JOIN age_groups a ON h.country = a.country AND h.gender = a.gender;

Правильно ли я сделал?

Снова OP - я понял, что возрастные группы и группы роста не совпадают в моем предыдущем ответе (например, иногда age_group = 1 будет иметь разный минимальный/максимальный рост).

Вот моя вторая попытка - более ручная попытка (на этот раз для 5 возрастных групп и 5 ростовых групп):

CREATE TABLE MY_TABLE (
    country VARCHAR(50),
    gender CHAR(1),
    age INTEGER,
    height FLOAT,
    owns_bicycle VARCHAR(3)
);

INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'M', 25, 175.99, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 63, 163.65, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 83, 166.01, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'M', 94, 178.92, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'M', 63, 173.24, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'M', 87, 156.27, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 89, 159.26, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'M', 61, 179.14, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 49, 167.55, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'F', 53, 172.82, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 62, 161.18, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 31, 173.08, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'F', 33, 166.13, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('Canada', 'F', 50, 177.42, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 57, 155.56, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'M', 57, 158.47, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 27, 152.85, 'No');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('UK', 'F', 89, 156.31, 'Yes');
INSERT INTO MY_TABLE (country, gender, age, height, owns_bicycle)
VALUES ('USA', 'F', 99, 179.31, 'No');

CREATE TABLE age_groups AS
SELECT country, gender, age, height, owns_bicycle,
NTILE(5) OVER (ORDER BY age) AS age_group
FROM MY_TABLE;

CREATE TABLE height_groups AS
SELECT country, gender, age, height, owns_bicycle,
NTILE(5) OVER (ORDER BY height) AS height_group
FROM MY_TABLE;

CREATE TABLE age_height_group_replacements AS
SELECT
    a.country,
    a.gender,
    a.age,
    a.height,
    a.owns_bicycle,
    CASE
        WHEN a.age BETWEEN (SELECT MIN(age) FROM age_groups WHERE age_group = 1) AND (SELECT MAX(age) FROM age_groups WHERE age_group = 1) THEN 'Group 1'
        WHEN a.age BETWEEN (SELECT MIN(age) FROM age_groups WHERE age_group = 2) AND (SELECT MAX(age) FROM age_groups WHERE age_group = 2) THEN 'Group 2'
        WHEN a.age BETWEEN (SELECT MIN(age) FROM age_groups WHERE age_group = 3) AND (SELECT MAX(age) FROM age_groups WHERE age_group = 3) THEN 'Group 3'
        WHEN a.age BETWEEN (SELECT MIN(age) FROM age_groups WHERE age_group = 4) AND (SELECT MAX(age) FROM age_groups WHERE age_group = 4) THEN 'Group 4'
        ELSE 'Group 5'
    END AS age_group_replacement,
    CASE
        WHEN a.height BETWEEN (SELECT MIN(height) FROM height_groups WHERE height_group = 1) AND (SELECT MAX(height) FROM height_groups WHERE height_group = 1) THEN 'Group 1'
        WHEN a.height BETWEEN (SELECT MIN(height) FROM height_groups WHERE height_group = 2) AND (SELECT MAX(height) FROM height_groups WHERE height_group = 2) THEN 'Group 2'
        WHEN a.height BETWEEN (SELECT MIN(height) FROM height_groups WHERE height_group = 3) AND (SELECT MAX(height) FROM height_groups WHERE height_group = 3) THEN 'Group 3'
        WHEN a.height BETWEEN (SELECT MIN(height) FROM height_groups WHERE height_group = 4) AND (SELECT MAX(height) FROM height_groups WHERE height_group = 4) THEN 'Group 4'
        ELSE 'Group 5'
    END AS height_group_replacement,
    (SELECT MIN(age) FROM age_groups WHERE age_group = (SELECT age_group FROM age_groups WHERE age = a.age)) AS min_age,
    (SELECT MAX(age) FROM age_groups WHERE age_group = (SELECT age_group FROM age_groups WHERE age = a.age)) AS max_age,
    (SELECT MIN(height) FROM height_groups WHERE height_group = (SELECT height_group FROM height_groups WHERE height = a.height)) AS min_height,
    (SELECT MAX(height) FROM height_groups WHERE height_group = (SELECT height_group FROM height_groups WHERE height = a.height)) AS max_height
FROM
    MY_TABLE a;

 CREATE TABLE BIKE_OWNERSHIP AS SELECT
    min_age,
    max_age,
    min_height,
    max_height,
    country,
    gender,
    COUNT(CASE WHEN owns_bicycle = 'Yes' THEN 1 END) * 100.0 / COUNT(*) AS percentage_owns_bicycle
FROM
    age_height_group_replacements
GROUP BY
    min_age,
    max_age,
    min_height,
    max_height,
    country,
    gender;

У кого-нибудь есть комментарии по этому поводу?

Спасибо!

Примечание: вы можете попробовать выполнить этот код SQL здесь: https://sqliteonline.com/