Les fonctions SQL de Log Service prennent en charge les modèles de classification - Simple Log Service

Les modèles d'apprentissage automatique de classification identifient en temps réel la classe des objets système, par exemple pour détecter des requêtes malveillantes ou découvrir des relations entre les éléments. Les sections suivantes décrivent la syntaxe et fournissent des exemples de fonctions d'analyse de classification.

Informations générales

L'index d'exemple suivant est utilisé dans les exemples de fonctions de classification. Pour plus d'informations, consultez Créer des index.

L'index d'exemple contient les 14 champs suivants : person_id (long, ID du patient), gender (texte, sexe), age (long, âge), occupation (texte, profession), sleep_duration (double, durée du sommeil), quality_of_sleep (long, qualité du sommeil), physical_activity_level (long, niveau d'activité physique), stress_level (long, niveau de stress), bmi_category (texte, catégorie d'IMC), blood_pressure_systolic (long, pression artérielle systolique), blood_pressure_diastolic (long, pression artérielle diastolique), heart_rate (long, fréquence cardiaque), daily_steps (long, nombre de pas quotidiens) et sleep_disorder (texte, trouble du sommeil). La fonctionnalité statistics est activée pour tous les champs.

Le code suivant présente un journal d'exemple :

1,Male,27,Software Engineer,6.1,6,42,6,Overweight,126,83,77,4200,None
2,Male,28,Doctor,6.2,6,60,8,Normal,125,80,75,10000,None
3,Male,28,Doctor,6.2,6,60,8,Normal,125,80,75,10000,None
4,Male,28,Sales Representative,5.9,4,30,8,Obese,140,90,85,3000,Sleep Apnea
5,Male,28,Sales Representative,5.9,4,30,8,Obese,140,90,85,3000,Sleep Apnea
6,Male,28,Software Engineer,5.9,4,30,8,Obese,140,90,85,3000,Insomnia
7,Male,29,Teacher,6.3,6,40,7,Obese,140,90,82,3500,Insomnia
8,Male,29,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
9,Male,29,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
10,Male,29,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
11,Male,29,Doctor,6.1,6,30,8,Normal,120,80,70,8000,None
12,Male,29,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
13,Male,29,Doctor,6.1,6,30,8,Normal,120,80,70,8000,None
14,Male,29,Doctor,6,6,30,8,Normal,120,80,70,8000,None
15,Male,29,Doctor,6,6,30,8,Normal,120,80,70,8000,None
16,Male,29,Doctor,6,6,30,8,Normal,120,80,70,8000,None
17,Female,29,Nurse,6.5,5,40,7,Normal Weight,132,87,80,4000,Sleep Apnea
18,Male,29,Doctor,6,6,30,8,Normal,120,80,70,8000,Sleep Apnea
19,Female,29,Nurse,6.5,5,40,7,Normal Weight,132,87,80,4000,Insomnia
20,Male,30,Doctor,7.6,7,75,6,Normal,120,80,70,8000,None
21,Male,30,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
22,Male,30,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
23,Male,30,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
24,Male,30,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
25,Male,30,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
26,Male,30,Doctor,7.9,7,75,6,Normal,120,80,70,8000,None
27,Male,30,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
28,Male,30,Doctor,7.9,7,75,6,Normal,120,80,70,8000,None
29,Male,30,Doctor,7.9,7,75,6,Normal,120,80,70,8000,None
30,Male,30,Doctor,7.9,7,75,6,Normal,120,80,70,8000,None
31,Female,30,Nurse,6.4,5,35,7,Normal Weight,130,86,78,4100,Sleep Apnea
32,Female,30,Nurse,6.4,5,35,7,Normal Weight,130,86,78,4100,Insomnia
33,Female,31,Nurse,7.9,8,75,4,Normal Weight,117,76,69,6800,None
34,Male,31,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
35,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
36,Male,31,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
37,Male,31,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
38,Male,31,Doctor,7.6,7,75,6,Normal,120,80,70,8000,None
39,Male,31,Doctor,7.6,7,75,6,Normal,120,80,70,8000,None
40,Male,31,Doctor,7.6,7,75,6,Normal,120,80,70,8000,None
41,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
42,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
43,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
44,Male,31,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
45,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
46,Male,31,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
47,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
48,Male,31,Doctor,7.8,7,75,6,Normal,120,80,70,8000,None
49,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
50,Male,31,Doctor,7.7,7,75,6,Normal,120,80,70,8000,Sleep Apnea
51,Male,32,Engineer,7.5,8,45,3,Normal,120,80,70,8000,None
52,Male,32,Engineer,7.5,8,45,3,Normal,120,80,70,8000,None
53,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
54,Male,32,Doctor,7.6,7,75,6,Normal,120,80,70,8000,None
55,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
56,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
57,Male,32,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
58,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
59,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
60,Male,32,Doctor,7.7,7,75,6,Normal,120,80,70,8000,None
61,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
62,Male,32,Doctor,6,6,30,8,Normal,125,80,72,5000,None
63,Male,32,Doctor,6.2,6,30,8,Normal,125,80,72,5000,None
64,Male,32,Doctor,6.2,6,30,8,Normal,125,80,72,5000,None
65,Male,32,Doctor,6.2,6,30,8,Normal,125,80,72,5000,None
66,Male,32,Doctor,6.2,6,30,8,Normal,125,80,72,5000,None
67,Male,32,Accountant,7.2,8,50,6,Normal Weight,118,76,68,7000,None
68,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,Insomnia
69,Female,33,Scientist,6.2,6,50,6,Overweight,128,85,76,5500,None
70,Female,33,Scientist,6.2,6,50,6,Overweight,128,85,76,5500,None
71,Male,33,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
72,Male,33,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
73,Male,33,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
74,Male,33,Doctor,6.1,6,30,8,Normal,125,80,72,5000,None
75,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,None
76,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,None
77,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,None
78,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,None
79,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,None
80,Male,33,Doctor,6,6,30,8,Normal,125,80,72,5000,None
81,Female,34,Scientist,5.8,4,32,8,Overweight,131,86,81,5200,Sleep Apnea
82,Female,34,Scientist,5.8,4,32,8,Overweight,131,86,81,5200,Sleep Apnea
83,Male,35,Teacher,6.7,7,40,5,Overweight,128,84,70,5600,None
84,Male,35,Teacher,6.7,7,40,5,Overweight,128,84,70,5600,None
85,Male,35,Software Engineer,7.5,8,60,5,Normal Weight,120,80,70,8000,None
86,Female,35,Accountant,7.2,8,60,4,Normal,115,75,68,7000,None
87,Male,35,Engineer,7.2,8,60,4,Normal,125,80,65,5000,None
88,Male,35,Engineer,7.2,8,60,4,Normal,125,80,65,5000,None
89,Male,35,Engineer,7.3,8,60,4,Normal,125,80,65,5000,None
90,Male,35,Engineer,7.3,8,60,4,Normal,125,80,65,5000,None
91,Male,35,Engineer,7.3,8,60,4,Normal,125,80,65,5000,None
92,Male,35,Engineer,7.3,8,60,4,Normal,125,80,65,5000,None
93,Male,35,Software Engineer,7.5,8,60,5,Normal Weight,120,80,70,8000,None
94,Male,35,Lawyer,7.4,7,60,5,Obese,135,88,84,3300,Sleep Apnea
95,Female,36,Accountant,7.2,8,60,4,Normal,115,75,68,7000,Insomnia
96,Female,36,Accountant,7.1,8,60,4,Normal,115,75,68,7000,None
97,Female,36,Accountant,7.2,8,60,4,Normal,115,75,68,7000,None
98,Female,36,Accountant,7.1,8,60,4,Normal,115,75,68,7000,None
99,Female,36,Teacher,7.1,8,60,4,Normal,115,75,68,7000,None
100,Female,36,Teacher,7.1,8,60,4,Normal,115,75,68,7000,None
101,Female,36,Teacher,7.2,8,60,4,Normal,115,75,68,7000,None

Fonctions

Les fonctions d'analyse de classification utilisent un modèle d'apprentissage automatique pour déterminer en temps réel la classe d'un objet système.

Fonction

Syntaxe

Description

Type de données de la valeur de retour

Fonction decision_tree_classifier

decision_tree_classifier(

target_variable varchar,

input_variable_array array(varchar),

target_variable_name varchar,

input_variable_name_array array(varchar),

input_variable_type_array array(varchar),

<optional> model_options varchar

)

Renvoie un modèle d'arbre de décision entraîné pour la classification des données et l'analyse des causes, basé sur les échantillons spécifiés.

varchar

Fonction decision_tree_predict

decision_tree_predict(

decision_tree_model_in_json varchar,

input_variable_array array(varchar)

)

Identifie les classes des objets système en fonction des échantillons spécifiés et du modèle d'arbre de décision renvoyé par la fonction decision_tree_classifier.

varchar

Fonction decision_tree_classifier

La fonction decision_tree_classifier renvoie un modèle d'arbre de décision entraîné pour la classification des données et l'analyse des causes, basé sur les échantillons spécifiés.

varchar decision_tree_classifier(target_variable varchar,input_variable_array array(varchar),target_variable_name varchar,input_variable_name_array array(varchar),input_variable_type_array array(varchar),<optional> model_options varchar)

Paramètre	Description
`target_variable`	Variable de sortie.
`input_variable_array`	Tableau des variables d'entrée. Chaque variable doit être convertie en chaîne.
`target_variable_name`	Nom de la variable de sortie.
`input_variable_name_array`	Tableau des noms des variables d'entrée.
`input_variable_type_array`	Tableau des types des variables d'entrée. Types de variables d'entrée pris en charge : `ID_STR` : identifiant de type chaîne pour l'objet de classification. `ID_NUM` : identifiant de type numérique pour l'objet de classification. `X_STR_CATEGORICAL` : données discrètes de type chaîne. Par exemple, les valeurs valides d'un champ sont Male, Female et Unknown. `X_NUM_CATEGORICAL` : données discrètes de type numérique. Par exemple, les valeurs valides d'un champ sont 1, 2, 3, 4 et 5. `X_NUMERIC` : données continues de type numérique.
`model_options`	Facultatif. Paramètres avancés spécifiés sous forme de chaîne de paires clé-valeur séparées par des virgules ou des points-virgules. Ce paramètre n'est généralement pas requis. Exemple : `criterion=gini,splitter=best,max_depth=100,min_samples_split=20,min_samples_leaf=10,min_weight_fraction_leaf=0,01,max_leaf_nodes=100000,min_impurity_decrease=0,01,class_weight=balanced`. Paramètres avancés pour le modèle d'arbre de décision : `criterion` : critère de division des nœuds. Définissez la valeur sur `gini`, `entropy` ou `log_loss`. `splitter` : stratégie utilisée pour diviser un nœud. Définissez la valeur sur `best` ou `random`. `max_depth` : profondeur maximale de l'arbre de décision. `min_samples_split` : nombre minimal d'échantillons requis pour diviser un nœud. `min_samples_leaf` : nombre minimal d'échantillons requis au niveau d'une feuille. `min_weight_fraction_leaf` : proportion minimale du poids de l'échantillon par rapport au poids total de l'échantillon. `max_leaf_nodes` : nombre maximal de nœuds feuilles. `min_impurity_decrease` : diminution d'impureté requise pour diviser un nœud. `class_weight` : poids de la classe. Définir cette valeur sur `balanced` permet de résoudre le déséquilibre des échantillons en rendant les poids des échantillons inversement proportionnels au nombre d'échantillons dans chaque classe. Plus une classe contient d'échantillons, plus le poids par échantillon est faible.

Exemple

Instruction de requête

* | with sleep_health_group_data as
(
  select g.group_id,
  s.*
  from (
    select 'G1' as group_id union all
    select 'G2' as group_id
  ) as g         -- Add a group_id field to show that decision_tree_classifier acts as an aggregate function.
  cross join log as s
)
select group_id,
    decision_tree_classifier(
      sleep_disorder,
      array[cast(person_id as varchar),
              cast(gender as varchar),
              cast(age as varchar),
              cast(occupation as varchar),
              cast(sleep_duration as varchar),
              cast(quality_of_sleep as varchar),
              cast(physical_activity_level as varchar),
              cast(stress_level as varchar),
              cast(bmi_category as varchar),
              cast(blood_pressure_systolic as varchar),
              cast(blood_pressure_diastolic as varchar),
              cast(heart_rate as varchar),
              cast(daily_steps as varchar)],
      'sleep_disorder',
      array['person_id', 'gender', 'age', 'occupation', 'sleep_duration',
              'quality_of_sleep', 'physical_activity_level', 'stress_level',
              'bmi_category', 'blood_pressure_systolic', 'blood_pressure_diastolic',
              'heart_rate', 'daily_steps'],
      array['ID_NUM',
              'X_STR_CATEGORICAL',
              'X_NUMERIC',
              'X_STR_CATEGORICAL',
              'X_NUMERIC',
              'X_NUMERIC',
              'X_NUMERIC',
              'X_NUMERIC',
              'X_STR_CATEGORICAL',
              'X_NUMERIC',
              'X_NUMERIC',
              'X_NUMERIC',
              'X_NUMERIC']
    ) as sleep_health_model
from sleep_health_group_data
group by group_id
order by group_id

Résultats de la requête et de l'analyse

Le champ sleep_health_model contient le modèle d'arbre de décision. Le champ decisionTreeEncode contient le modèle d'arbre de décision sérialisé. Utilisez ce modèle dans la fonction decision_tree_predict pour identifier la classe d'un objet système.

group_id

sleep_health_model

{
  "returnCode": 0,
  "message": "OK",
  "decisionTreeEncode": "gANjc2tsZWFybi50cmVlLl9jbGFzc2VzCkRlY2lzaW9uVHJlZUNsYXNzaWZpZXIKcQApgXEBfXECKFgJAAAAY3JpdGVyaW9ucQNYBAAAAGdpbmlxBFgIAAAAc3BsaXR0ZXJxBVgEAAAAYmVzdHEGWAkAAABtYXhfZGVwdGhxB05YEQAAAG1pbl9zYW1wbGVzX3NwbGl0cQhLFFgQAAAAbWluX3NhbXBsZXNfbGVhZnEJSwpYGAAAAG1pbl93ZWlnaHRfZnJhY3Rpb25fbGVhZnEKRwAAAAAAAAAAWAwAAABtYXhfZmVhdHVyZXNxC05YDgAAAG1heF9sZWFmX25vZGVzcQxOWAwAAAByYW5kb21fc3RhdGVxDU5YFQAAAG1pbl9pbXB1cml0eV9kZWNyZWFzZXEORz+EeuFHrhR7WBIAAABtaW5faW1wdXJpdHlfc3BsaXRxD05YDAAAAGNsYXNzX3dlaWdodHEQWAgAAABiYWxhbmNlZHERWAkAAABjY3BfYWxwaHFGEgcAAAAAAAAAAFgOAAAAbl9mZWF0dXJlc19pbl9xE0sXWAsAAABuX2ZlYXR1cmVzX3EUSxdYCgAAAG5fb3V0cHV0c19xFUsBWAgAAABjbGFzc2VzX3EWY251bXB5LmNvcmUubXVsdGlhcnJheQpfcmVjb25zdHJ1Y3QKcRdjbnVtcHkKbmRhcnJheQpxGEsAhXEZQwFicRqHcRtScRwoSwFLA4VxHWNudW1weQpkdHlwZQpxHlgDAAAAVTExcR+JiIdxIFJxIShLA1gBAAAAPHEiTk5OSyxLBEsIdHEjYolDhEkAAABuAAAAcwAAAG8AAABtAAAAbgAAAGkAAABhAAAAAAAAAAAAAAAAAAAATgAAAG8AAABuAAAAZQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABTAAAAbAAAAGUAAABlAAAAcAAAACAAAABBAAAAcAAAAG4AAABlAAAAYQAAAHEkdHElYlgKAAAAbl9jbGFzc2VzX3EmY251bXB5LmNvcmUubXVsdGlhcnJheQpzY2FsYXIKcSdoHlgCAAAAaThxKImIh3EpUnEqKEsDaCJOTk5K/////0r/////SwB0cStiQwgDAAAAAAAAAHEshnEtUnEuWA0AAABtYXhfZmVhdHVyZXNfcS9LF1gFAAAAdHJlZV9xMGNza2xlYXJuLnRyZWUuX3RyZWUKVHJlZQpxMUsXaBdoGEsAhXEyaBqHcTNScTQoSwFLAYVxNWgqiUMIAwAAAAAAAABxNnRxN2JLAYdxOFJxOX1xOihoB0sEWAoAAABub2RlX2NvdW50cTtLCVgFAAAAbm9kZXNxPGgXaBhLAIVxPWgah3E+UnE/KEsBSwmFcUBoHlgDAAAAVjU2cUGJiIdxQlJxQyhLA1gBAAAAfHFETihYCgAAAGxlZnRfY2hpbGRxRVgLAAAAcmlnaHRfY2hpbGRxRlgHAAAAZmVhdHVyZXFHWAkAAAB0aHJlc2hvbGRxSFgIAAAAaW1wdXJpdHlxSVgOAAAAbl9ub2RlX3NhbXBsZXNxSlgXAAAAd2VpZ2h0ZWRfbl9ub2RlX3NhbXBsZXNxS3RxTH1xTShoRWgeWAIAAABpOHFOiYiHcU9ScVAoSwNoIk5OTkr/////Sv////9LAHRxUWJLAIZxUmhGaFBLCIZxU2hHaFBLEIZxVGhIaB5YAgAAAGY4cVWJiIdxVlJxVyhLA2giTk5OSv////9K/////0sAdHFYYksYhnFZaEloV0sghnFaaEpoUEsohnFbaEtoV0swhnFcdUs4SwFLEHRxXWKJQvgBAAABAAAAAAAAAAgAAAAAAAAAFAAAAAAAAAAAAAAAAGBXQIxVVVVVVeU/7AIAAAAAAAAjAAAAAGCHQAIAAAAAAAAABwAAAAAAAAAQAAAAAAAAAAAAAAAAAOA/Os7p5i3y4j9qAgAAAAAAAFi6jsHEM4FAAwAAAAAAAAAEAAAAAAAAABMAAAAAAAAAAAAAAAAQYEDSdORBSg3bP+QAAAAAAAAAZbNva5f3ckD//////////////////////v////////8AAAAAAAAAwAAAAAAAAAAANgAAAAAAAADdq1evXr0+QAUAAAAAAAAABgAAAAAAAAAWAAAAAAAAAAAAAAAA4LVAtALg68NO0z+uAAAAAAAAALo4eoDBC3FA//////////////////////7/////////AAAAAAAAAMBsIUDNeQPfPywAAAAAAAAAzbIsy7KsUUD//////////////////////v////////8AAAAAAAAAwBCB6oIYErM/ggAAAAAAAAAXGF6bKUFpQP/////////////////////+/////////wAAAAAAAADAemDLWrmn0T+GAQAAAAAAAK+BWy/k325A//////////////////////7/////////AAAAAAAAAMCIcoA8/1e2P4IAAAAAAAAABRfF+eywaEBxXnRxX2JYBgAAAHZhbHVlc3FgaBdoGEsAhXFhaBqHcWJScWMoSwFLCUsBSwOHcWRoV4lD2LGqqqqqKm9AUaqqqqoqb0CYqqqqqipvQDcMwzAMw25A81YgXYF0bkDeFV7hFV5OQOG2bdu27WtAXnpm6ZmlQUAERmAERmBGQAAAAAAAAAAA3atXr169PkAAAAAAAAAAAOG2bdu27WtAdSPVjVQ3EkAERmAERmBGQEySJEmSJD1AAAAAAAAAAAByHMdxHMdEQJckSZIkSWhAdSPVjVQ3EkAqmZIpmZIJQKyqqqqqqjZAebjG4hoLakBzP/dzP/cvQHqe53me5wlAU2xKsSnFFkAsJVMyJZNnQHFldHFmYnViWBAAAABfc2tsZWFybl92ZXJzaW9ucWdYBgAAADAuMjQuMnFodWIu",
  "decisionTreeInText": "|--- blood_pressure_diastolic \u003c\u003d 93.50\n|   |--- bmi_category.Normal \u003c\u003d 0.50\n|   |   |--- blood_pressure_systolic \u003c\u003d 128.50\n|   |   |   |--- class: None\n|   |   |--- blood_pressure_systolic \u003e  128.50\n|   |   |   |--- daily_steps \u003c\u003d 5600.00\n|   |   |   |   |--- class: Sleep Apnea\n|   |   |   |--- daily_steps \u003e  5600.00\n|   |   |   |   |--- class: Insomnia\n|   |--- bmi_category.Normal \u003e  0.50\n|   |   |--- class: None\n|--- blood_pressure_diastolic \u003e  93.50\n|   |--- class: Sleep Apnea\n",
  "uniqueLabels": [
    "Insomnia",
    "None",
    "Sleep Apnea"
  ],
  "confusionMatrix": [
    [
      120,
      14,
      20
    ],
    [
      8,
      420,
      10
    ],
    [
      2,
      10,
      144
    ]
  ],
  "dataColumnNames": [
    "person_id",
    "gender",
    "age",
    "occupation",
    "sleep_duration",
    "quality_of_sleep",
    "physical_activity_level",
    "stress_level",
    "bmi_category",
    "blood_pressure_systolic",
    "blood_pressure_diastolic",
    "heart_rate",
    "daily_steps",
    "sleep_disorder"
  ],
  "dataColumnTypes": {
    "occupation": "X_STR_CATEGORICAL",
    "blood_pressure_diastolic": "X_NUMERIC",
    "gender": "X_STR_CATEGORICAL",
    "heart_rate": "X_NUMERIC",
    "blood_pressure_systolic": "X_NUMERIC",
    "stress_level": "X_NUMERIC",
    "daily_steps": "X_NUMERIC",
    "physical_activity_level": "X_NUMERIC",
    "bmi_category": "X_STR_CATEGORICAL",
    "sleep_duration": "X_NUMERIC",
    "quality_of_sleep": "X_NUMERIC",
    "sleep_disorder": "Y_STR_CATEGORICAL",
    "age": "X_NUMERIC",
    "person_id": "ID_NUM"
  },
  "categoricalVariableValues": {
    "bmi_category": [
      "Normal",
      "Normal Weight",
      "Obese",
      "Overweight"
    ],
    "gender": [
      "Female",
      "Male"
    ],
    "occupation": [
      "Accountant",
      "Doctor",
      "Engineer",
      "Lawyer",
      "Manager",
      "Nurse",
      "Sales Representative",
      "Salesperson",
      "Scientist",
      "Software Engineer",
      "Teacher"
    ]
  }
}

{
  "returnCode": 0,
  "message": "OK",
  "decisionTreeEncode": "gANjc2tsZWFybi50cmVlLl9jbGFzc2VzCkRlY2lzaW9uVHJlZUNsYXNzaWZpZXIKcQApgXEBfXECKFgJAAAAY3JpdGVyaW9ucQNYBAAAAGdpbmlxBFgIAAAAc3BsaXR0ZXJxBVgEAAAAYmVzdHEGWAkAAABtYXhfZGVwdGhxB05YEQAAAG1pbl9zYW1wbGVzX3NwbGl0cQhLFFgQAAAAbWluX3NhbXBsZXNfbGVhZnEJSwpYGAAAAG1pbl93ZWlnaHRfZnJhY3Rpb25fbGVhZnEKRwAAAAAAAAAAWAwAAABtYXhfZmVhdHVyZXNxC05YDgAAAG1heF9sZWFmX25vZGVzcQxOWAwAAAByYW5kb21fc3RhdGVxDU5YFQAAAG1pbl9pbXB1cml0eV9kZWNyZWFzZXEORz+EeuFHrhR7WBIAAABtaW5faW1wdXJpdHlfc3BsaXRxD05YDAAAAGNsYXNzX3dlaWdodHEQWAgAAABiYWxhbmNlZHERWAkAAABjY3BfYWxwaHFGEgcAAAAAAAAAAFgOAAAAbl9mZWF0dXJlc19pbl9xE0sXWAsAAABuX2ZlYXR1cmVzX3EUSxdYCgAAAG5fb3V0cHV0c19xFUsBWAgAAABjbGFzc2VzX3EWY251bXB5LmNvcmUubXVsdGlhcnJheQpfcmVjb25zdHJ1Y3QKcRdjbnVtcHkKbmRhcnJheQpxGEsAhXEZQwFicRqHcRtScRwoSwFLA4VxHWNudW1weQpkdHlwZQpxHlgDAAAAVTExcR+JiIdxIFJxIShLA1gBAAAAPHEiTk5OSyxLBEsIdHEjYolDhEkAAABuAAAAcwAAAG8AAABtAAAAbgAAAGkAAABhAAAAAAAAAAAAAAAAAAAATgAAAG8AAABuAAAAZQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABTAAAAbAAAAGUAAABlAAAAcAAAACAAAABBAAAAcAAAAG4AAABlAAAAYQAAAHEkdHElYlgKAAAAbl9jbGFzc2VzX3EmY251bXB5LmNvcmUubXVsdGlhcnJheQpzY2FsYXIKcSdoHlgCAAAAaThxKImIh3EpUnEqKEsDaCJOTk5K/////0r/////SwB0cStiQwgDAAAAAAAAAHEshnEtUnEuWA0AAABtYXhfZmVhdHVyZXNfcS9LF1gFAAAAdHJlZV9xMGNza2xlYXJuLnRyZWUuX3RyZWUKVHJlZQpxMUsXaBdoGEsAhXEyaBqHcTNScTQoSwFLAYVxNWgqiUMIAwAAAAAAAABxNnRxN2JLAYdxOFJxOX1xOihoB0sEWAoAAABub2RlX2NvdW50cTtLCVgFAAAAbm9kZXNxPGgXaBhLAIVxPWgah3E+UnE/KEsBSwmFcUBoHlgDAAAAVjU2cUGJiIdxQlJxQyhLA1gBAAAAfHFETihYCgAAAGxlZnRfY2hpbGRxRVgLAAAAcmlnaHRfY2hpbGRxRlgHAAAAZmVhdHVyZXFHWAkAAAB0aHJlc2hvbGRxSFgIAAAAaW1wdXJpdHlxSVgOAAAAbl9ub2RlX3NhbXBsZXNxSlgXAAAAd2VpZ2h0ZWRfbl9ub2RlX3NhbXBsZXNxS3RxTH1xTShoRWgeWAIAAABpOHFOiYiHcU9ScVAoSwNoIk5OTkr/////Sv////9LAHRxUWJLAIZxUmhGaFBLCIZxU2hHaFBLEIZxVGhIaB5YAgAAAGY4cVWJiIdxVlJxVyhLA2giTk5OSv////9K/////0sAdHFYYksYhnFZaEloV0sghnFaaEpoUEsohnFbaEtoV0swhnFcdUs4SwFLEHRxXWKJQvgBAAABAAAAAAAAAAgAAAAAAAAAFAAAAAAAAAAAAAAAAGBXQIxVVVVVVeU/7AIAAAAAAAAjAAAAAGCHQAIAAAAAAAAABwAAAAAAAAAQAAAAAAAAAAAAAAAAAOA/Os7p5i3y4j9qAgAAAAAAAFi6jsHEM4FAAwAAAAAAAAAEAAAAAAAAABMAAAAAAAAAAAAAAAAQYEDSdORBSg3bP+QAAAAAAAAAZbNva5f3ckD//////////////////////v////////8AAAAAAAAAwAAAAAAAAAAANgAAAAAAAADdq1evXr0+QAUAAAAAAAAABgAAAAAAAAAWAAAAAAAAAAAAAAAA4LVAtALg68NO0z+uAAAAAAAAALo4eoDBC3FA//////////////////////7/////////AAAAAAAAAMBsIUDNeQPfPywAAAAAAAAAzbIsy7KsUUD//////////////////////v////////8AAAAAAAAAwBCB6oIYErM/ggAAAAAAAAAXGF6bKUFpQP/////////////////////+/////////wAAAAAAAADAemDLWrmn0T+GAQAAAAAAAK+BWy/k325A//////////////////////7/////////AAAAAAAAAMCIcoA8/1e2P4IAAAAAAAAABRfF+eywaEBxXnRxX2JYBgAAAHZhbHVlc3FgaBdoGEsAhXFhaBqHcWJScWMoSwFLCUsBSwOHcWRoV4lD2LGqqqqqKm9AUaqqqqoqb0CYqqqqqipvQDcMwzAMw25A81YgXYF0bkDeFV7hFV5OQOG2bdu27WtAXnpm6ZmlQUAERmAERmBGQAAAAAAAAAAA3atXr169PkAAAAAAAAAAAOG2bdu27WtAdSPVjVQ3EkAERmAERmBGQEySJEmSJD1AAAAAAAAAAAByHMdxHMdEQJckSZIkSWhAdSPVjVQ3EkAqmZIpmZIJQKyqqqqqqjZAebjG4hoLakBzP/dzP/cvQHqe53me5wlAU2xKsSnFFkAsJVMyJZNnQHFldHFmYnViWBAAAABfc2tsZWFybl92ZXJzaW9ucWdYBgAAADAuMjQuMnFodWIu",
  "decisionTreeInText": "|--- blood_pressure_diastolic \u003c\u003d 93.50\n|   |--- bmi_category.Normal \u003c\u003d 0.50\n|   |   |--- blood_pressure_systolic \u003c\u003d 128.50\n|   |   |   |--- class: None\n|   |   |--- blood_pressure_systolic \u003e  128.50\n|   |   |   |--- daily_steps \u003c\u003d 5600.00\n|   |   |   |   |--- class: Sleep Apnea\n|   |   |   |--- daily_steps \u003e  5600.00\n|   |   |   |   |--- class: Insomnia\n|   |--- bmi_category.Normal \u003e  0.50\n|   |   |--- class: None\n|--- blood_pressure_diastolic \u003e  93.50\n|   |--- class: Sleep Apnea\n",
  "uniqueLabels": [
    "Insomnia",
    "None",
    "Sleep Apnea"
  ],
  "confusionMatrix": [
    [
      120,
      14,
      20
    ],
    [
      8,
      420,
      10
    ],
    [
      2,
      10,
      144
    ]
  ],
  "dataColumnNames": [
    "person_id",
    "gender",
    "age",
    "occupation",
    "sleep_duration",
    "quality_of_sleep",
    "physical_activity_level",
    "stress_level",
    "bmi_category",
    "blood_pressure_systolic",
    "blood_pressure_diastolic",
    "heart_rate",
    "daily_steps",
    "sleep_disorder"
  ],
  "dataColumnTypes": {
    "occupation": "X_STR_CATEGORICAL",
    "blood_pressure_diastolic": "X_NUMERIC",
    "gender": "X_STR_CATEGORICAL",
    "heart_rate": "X_NUMERIC",
    "blood_pressure_systolic": "X_NUMERIC",
    "stress_level": "X_NUMERIC",
    "daily_steps": "X_NUMERIC",
    "physical_activity_level": "X_NUMERIC",
    "bmi_category": "X_STR_CATEGORICAL",
    "sleep_duration": "X_NUMERIC",
    "quality_of_sleep": "X_NUMERIC",
    "sleep_disorder": "Y_STR_CATEGORICAL",
    "age": "X_NUMERIC",
    "person_id": "ID_NUM"
  },
  "categoricalVariableValues": {
    "bmi_category": [
      "Normal",
      "Normal Weight",
      "Obese",
      "Overweight"
    ],
    "gender": [
      "Female",
      "Male"
    ],
    "occupation": [
      "Accountant",
      "Doctor",
      "Engineer",
      "Lawyer",
      "Manager",
      "Nurse",
      "Sales Representative",
      "Salesperson",
      "Scientist",
      "Software Engineer",
      "Teacher"
    ]
  }
}

Fonction decision_tree_predict

La fonction decision_tree_predict identifie les classes des objets système en fonction des échantillons spécifiés et du modèle d'arbre de décision renvoyé.

varchar decision_tree_predict(decision_tree_model_in_json varchar,input_variable_array array(varchar))

Paramètre

Description

decision_tree_model_in_json

Valeur de retour de la fonction decision_tree_classifier.

input_variable_array

Tableau des variables d'entrée utilisées pour la classification. La fonction convertit les variables d'entrée en un tableau unidimensionnel.

Exemple

Instruction de requête

* | with model as
(
    select 'G1' as group_id,
        '{"returnCode":0,"message":"OK","decisionTree":{"nodeKey":0,"parentNodeKey":-1,"isLeaf":false,"numSamplesByClass":[124.66666666666676,124.66666666666688,124.66666666666683],"numSamples":374.00000000000045,"probabilitiesByClass":[0.33333333333333315,0.33333333333333354,0.33333333333333337],"predictedClass":"None","predictedClassProbability":0.33333333333333354,"splittingFeature":"blood_pressure_diastolic","threshold":93.5,"depth":1,"leftChild":{"nodeKey":1,"parentNodeKey":0,"isLeaf":false,"numSamplesByClass":[123.04761904761914,121.82039573820417,30.367521367521377],"numSamples":275.2355361533447,"probabilitiesByClass":[0.4470629801925882,0.4426041689265487,0.11033285088086307],"predictedClass":"Insomnia","predictedClassProbability":0.4470629801925882,"splittingFeature":"bmi_category.Normal","threshold":0.5,"depth":2,"leftChild":{"nodeKey":2,"parentNodeKey":1,"isLeaf":false,"numSamplesByClass":[111.7142857142858,17.646879756468795,22.37606837606838],"numSamples":151.737233846823,"probabilitiesByClass":[0.7362351539046778,0.11629894198732474,0.14746590410799743],"predictedClass":"Insomnia","predictedClassProbability":0.7362351539046778,"splittingFeature":"blood_pressure_systolic","threshold":128.5,"depth":3,"leftChild":{"nodeKey":3,"parentNodeKey":2,"isLeaf":true,"numSamplesByClass":[0.0,15.369863013698625,0.0],"numSamples":15.369863013698625,"probabilitiesByClass":[0.0,1.0,0.0],"predictedClass":"None","predictedClassProbability":1.0,"threshold":0.0,"depth":4},"rightChild":{"nodeKey":4,"parentNodeKey":2,"isLeaf":false,"numSamplesByClass":[111.7142857142858,2.2770167427701673,22.37606837606838],"numSamples":136.36737083312434,"probabilitiesByClass":[0.8192156601082596,0.016697665496217574,0.16408667439552274],"predictedClass":"Insomnia","predictedClassProbability":0.8192156601082596,"splittingFeature":"daily_steps","threshold":5600.0,"depth":4,"leftChild":{"nodeKey":5,"parentNodeKey":4,"isLeaf":true,"numSamplesByClass":[14.57142857142857,0.0,20.77777777777778],"numSamples":35.34920634920635,"probabilitiesByClass":[0.41221374045801523,0.0,0.5877862595419848],"predictedClass":"Sleep Apnea","predictedClassProbability":0.5877862595419848,"threshold":0.0,"depth":5},"rightChild":{"nodeKey":6,"parentNodeKey":4,"isLeaf":true,"numSamplesByClass":[97.14285714285721,2.2770167427701673,1.5982905982905984],"numSamples":101.01816448391799,"probabilitiesByClass":[0.9616375197385643,0.022540666368301186,0.015821813893134487],"predictedClass":"Insomnia","predictedClassProbability":0.9616375197385643,"threshold":0.0,"depth":5}}},"rightChild":{"nodeKey":7,"parentNodeKey":1,"isLeaf":true,"numSamplesByClass":[11.333333333333332,104.17351598173533,7.9914529914529915],"numSamples":123.49830230652165,"probabilitiesByClass":[0.09176914274662742,0.8435218463422892,0.06470901091108344],"predictedClass":"None","predictedClassProbability":0.8435218463422892,"threshold":0.0,"depth":3}},"rightChild":{"nodeKey":8,"parentNode Key":0,"isLeaf":true,"numSamplesByClass":[1.619047619047619,2.846270928462709,94.29914529914537],"numSamples":98.7644638466557,"probabilitiesByClass":[0.016393017852670114,0.028818775677068465,0.9547882064702613],"predictedClass":"Sleep Apnea","predictedClassProbability":0.9547882064702613,"threshold":0.0,"depth":2}},"decisionTreeClassLabels":["Insomnia","None","Sleep Apnea"],"decisionTreeEncode":"gASVgwcAAAAAAACMFXNrbGVhcm4udHJlZS5fY2xhc3Nlc5SMFkRlY2lzaW9uVHJlZUNsYXNzaWZpZXKUk5QpgZR9lCiMCWNyaXRlcmlvbpSMBGdpbmmUjAhzcGxpdHRlcpSMBGJlc3SUjAltYXhfZGVwdGiUTowRbWluX3NhbXBsZXNfc3BsaXSUSxSMEG1pbl9zYW1wbGVzX2xlYWaUSwqMGG1pbl93ZWlnaHRfZnJhY3Rpb25fbGVhZpRHP4R64UeuFHuMDG1heF9mZWF0dXJlc5ROjA5tYXhfbGVhZl9ub2Rlc5ROjAxyYW5kb21fc3RhdGWUTowVbWluX2ltcHVyaXR5X2RlY3JlYXNllEc/hHrhR64Ue4wMY2xhc3Nfd2VpZ2h0lIwIYmFsYW5jZWSUjAljY3BfYWxwaGGURwAAAAAAAAAAjA5uX2ZlYXR1cmVzX2luX5RLF4wKbl9vdXRwdXRzX5RLAYwIY2xhc3Nlc1+UjBVudW1weS5jb3JlLm11bHRpYXJyYXmUjAxfcmVjb25zdHJ1Y3SUk5SMBW51bXB5lIwHbmRhcnJheZSTlEsAhZRDAWKUh5RSlChLAUsDhZRoGowFZHR5cGWUk5SMA1UxMZSJiIeUUpQoSwOMATyUTk5OSyxLBEsIdJRiiUOESQAAAG4AAABzAAAAbwAAAG0AAABuAAAAaQAAAGEAAAAAAAAAAAAAAAAAAABOAAAAbwAAAG4AAABlAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFMAAABsAAAAZQAAAGUAAABwAAAAIAAAAEEAAABwAAAAbgAAAGUAAABhAAAAlHSUYowKbl9jbGFzc2VzX5RoF4wGc2NhbGFylJOUaCOMAmk4lImIh5RSlChLA2gnTk5OSv////9K/////0sAdJRiQwgDAAAAAAAAAJSGlFKUjA1tYXhfZmVhdHVyZXNflEsXjAV0cmVlX5SMEnNrbGVhcm4udHJlZS5fdHJlZZSMBFRyZWWUk5RLF2gZaBxLAIWUaB6HlFKUKEsBSwGFlGgwiUMIAwAAAAAAAACUdJRiSwGHlFKUfZQoaAlLBIwKbm9kZV9jb3VudJRLCYwFbm9kZXOUaBloHEsAhZRoHoeUUpQoSwFLCYWUaCOMA1Y1NpSJiIeUUpQoSwOMAXyUTiiMCmxlZnRfY2hpbGSUjAtyaWdodF9jaGlsZJSMB2ZlYXR1cmWUjAl0aHJlc2hvbGSUjAhpbXB1cml0eZSMDm5fbm9kZV9zYW1wbGVzlIwXd2VpZ2h0ZWRfbl9ub2RlX3NhbXBsZXOUdJR9lChoTWgjjAJpOJSJiIeUUpQoSwNoJ05OTkr/////Sv////9LAHSUYksAhpRoTmhYSwiGlGhPaFhLEIaUaFBoI4wCZjiUiYiHlFKUKEsDaCdOTk5K/////0r/////SwB0lGJLGIaUaFFoX0sghpRoUmhYSyiGlGhTaF9LMIaUdUs4SwFLEHSUYolC+AEAAAEAAAAAAAAACAAAAAAAAAAUAAAAAAAAAAAAAAAAYFdAS1VVVVVV5T92AQAAAAAAAP3/////X3dAAgAAAAAAAAAHAAAAAAAAABAAAAAAAAAAAAAAAAAA4D8xzunmLfLiPzUBAAAAAAAAKbqOwcQzcUADAAAAAAAAAAQAAAAAAAAAEwAAAAAAAAAAAAAAABBgQBx15EFKDds/cgAAAAAAAAB4s29rl/diQP/////////////////////+/////////wAAAAAAAADAAAAAAAAAAAAbAAAAAAAAANOrV69evS5ABQAAAAAAAAAGAAAAAAAAABYAAAAAAAAAAAAAAADgtUAeA+Drw07TP1cAAAAAAAAAvjh6gMELYUD//////////////////////v////////8AAAAAAAAAwHAhQM15A98/FgAAAAAAAADNsizLsqxBQP/////////////////////+/////////wAAAAAAAADAqIHqghgSsz9BAAAAAAAAABcYXpspQVlA//////////////////////7/////////AAAAAAAAAMBwXMtauafRP8MAAAAAAAAADIJbL+TfXkD//////////////////////v////////8AAAAAAAAAwPBegDz/V7Y/QQAAAAAAAAAUF8X57LBYQJR0lGKMBnZhbHVlc5RoGWgcSwCFlGgeh5RSlChLAUsJSwFLA4eUaF+JQ9ixqqqqqipfQLqqqqqqKl9AtqqqqqoqX0A3DMMwDMNeQFdXIF2BdF5A5BVe4RVePkDhtm3btu1bQFl6ZumZpTFABUZgBEZgNkAAAAAAAAAAANOrV69evS5AAAAAAAAAAADhtm3btu1bQHUj1Y1UNwJABUZgBEZgNkBIkiRJkiQtQAAAAAAAAAAAchzHcRzHNECXJEmSJElYQHUj1Y1UNwJAKpmSKZmS+T+qqqqqqqomQL64xuIaC1pAdD/3cz/3H0B6nud5nuf5P1JsSrEpxQZANyVTMiWTV0CUdJRidWKMEF9za2xlYXJuX3ZlcnNpb26UjAUxLjIuMpR1Yi4\u003d","decisionTreeInText":"|--- blood_pressure_diastolic \u003c\u003d 93.50\n|   |--- bmi_category.Normal \u003c\u003d 0.50\n|   |   |--- blood_pressure_systolic \u003c\u003d 128.50\n|   |   |   |--- class: None\n|   |   |--- blood_pressure_systolic \u003e  128.50\n|   |   |   |--- daily_steps \u003c\u003d 5600.00\n|   |   |   |   |--- class: Sleep Apnea\n|   |   |   |--- daily_steps \u003e  5600.00\n|   |   |   |   |--- class: Insomnia\n|   |--- bmi_category.Normal \u003e  0.50\n|   |   |--- class: None\n|--- blood_pressure_diastolic \u003e  93.50\n|   |--- class: Sleep Apnea\n","uniqueLabels":["Insomnia","None","Sleep Apnea"],"confusionMatrix":[[60,7,10],[4,210,5],[1,5,72]],"dataColumnNames":["person_id","gender","age","occupation","sleep_duration","quality_of_sleep","physical_activity_level","stress_level","bmi_category","blood_pressure_systolic","blood_pressure_diastolic","heart_rate","daily_steps","sleep_disorder"],"expandedColumnNames":["gender.Female","age","occupation.Accountant","occupation.Doctor","occupation.Engineer","occupation.Lawyer","occupation.Manager","occupation.Nurse","occupation.Sales Representative","occupation.Salesperson","occupation.Scientist","occupation.Software Engineer","sleep_duration","quality_of_sleep","physical_activity_level","stress_level","bmi_category.Normal","bmi_category.Normal Weight","bmi_category.Obese","blood_pressure_systolic","blood_pressure_diastolic","heart_rate","daily_steps"],"dataColumnTypes":{"occupation":"X_STR_CATEGORICAL","blood_pressure_diastolic":"X_NUMERIC","gender":"X_STR_CATEGORICAL","heart_rate":"X_NUMERIC","blood_pressure_systolic":"X_NUMERIC","stress_level":"X_NUMERIC","daily_steps":"X_NUMERIC","physical_activity_level":"X_NUMERIC","bmi_category":"X_STR_CATEGORICAL","sleep_duration":"X_NUMERIC","quality_of_sleep":"X_NUMERIC","sleep_disorder":"Y_STR_CATEGORICAL","age":"X_NUMERIC","person_id":"ID_NUM"},"categoricalVariableValues":{"bmi_category":["Normal","Normal Weight","Obese","Overweight"],"gender":["Female","Male"],"occupation":["Accountant","Doctor","Engineer","Lawyer","Manager","Nurse","Sales Representative","Salesperson","Scientist","Software Engineer","Teacher"]},"modelVersion":"1.0.0-20230821"}' as decision_tree_model,
        count(*) as record_count
    from log
),
sleep_health_data as
(
    select 1 as person_id, 'Male' as gender, 27 as age, 'Software Engineer' as occupation, 6.1 as sleep_duration, 6 as quality_of_sleep, 42 as physical_activity_level, 6 as stress_level, 'Overweight' as bmi_category, 126 as blood_pressure_systolic, 83 as blood_pressure_diastolic, 77 as heart_rate, 4200 as daily_steps, 'None' as sleep_disorder
    union all select 2 as person_id, 'Male' as gender, 28 as age, 'Doctor' as occupation, 6.2 as sleep_duration, 6 as quality_of_sleep, 60 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 125 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 75 as heart_rate, 10000 as daily_steps, 'None' as sleep_disorder
    union all select 3 as person_id, 'Male' as gender, 28 as age, 'Doctor' as occupation, 6.2 as sleep_duration, 6 as quality_of_sleep, 60 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 125 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 75 as heart_rate, 10000 as daily_steps, 'None' as sleep_disorder
    union all select 4 as person_id, 'Male' as gender, 28 as age, 'Sales Representative' as occupation, 5.9 as sleep_duration, 4 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Obese' as bmi_category, 140 as blood_pressure_systolic, 90 as blood_pressure_diastolic, 85 as heart_rate, 3000 as daily_steps, 'Sleep Apnea' as sleep_disorder
    union all select 5 as person_id, 'Male' as gender, 28 as age, 'Sales Representative' as occupation, 5.9 as sleep_duration, 4 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Obese' as bmi_category, 140 as blood_pressure_systolic, 90 as blood_pressure_diastolic, 85 as heart_rate, 3000 as daily_steps, 'Sleep Apnea' as sleep_disorder
    union all select 6 as person_id, 'Male' as gender, 28 as age, 'Software Engineer' as occupation, 5.9 as sleep_duration, 4 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Obese' as bmi_category, 140 as blood_pressure_systolic, 90 as blood_pressure_diastolic, 85 as heart_rate, 3000 as daily_steps, 'Insomnia' as sleep_disorder
    union all select 7 as person_id, 'Male' as gender, 29 as age, 'Teacher' as occupation, 6.3 as sleep_duration, 6 as quality_of_sleep, 40 as physical_activity_level, 7 as stress_level, 'Obese' as bmi_category, 140 as blood_pressure_systolic, 90 as blood_pressure_diastolic, 82 as heart_rate, 3500 as daily_steps, 'Insomnia' as sleep_disorder
    union all select 8 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 7.8 as sleep_duration, 7 as quality_of_sleep, 75 as physical_activity_level, 6 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 9 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 7.8 as sleep_duration, 7 as quality_of_sleep, 75 as physical_activity_level, 6 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 10 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 7.8 as sleep_duration, 7 as quality_of_sleep, 75 as physical_activity_level, 6 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 11 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 6.1 as sleep_duration, 6 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 12 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 7.8 as sleep_duration, 7 as quality_of_sleep, 75 as physical_activity_level, 6 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 13 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 6.1 as sleep_duration, 6 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 14 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 6 as sleep_duration, 6 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 15 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 6 as sleep_duration, 6 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 16 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 6 as sleep_duration, 6 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
    union all select 17 as person_id, 'Female' as gender, 29 as age, 'Nurse' as occupation, 6.5 as sleep_duration, 5 as quality_of_sleep, 40 as physical_activity_level, 7 as stress_level, 'Normal Weight' as bmi_category, 132 as blood_pressure_systolic, 87 as blood_pressure_diastolic, 80 as heart_rate, 4000 as daily_steps, 'Sleep Apnea' as sleep_disorder
    union all select 18 as person_id, 'Male' as gender, 29 as age, 'Doctor' as occupation, 6 as sleep_duration, 6 as quality_of_sleep, 30 as physical_activity_level, 8 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'Sleep Apnea' as sleep_disorder
    union all select 19 as person_id, 'Female' as gender, 29 as age, 'Nurse' as occupation, 6.5 as sleep_duration, 5 as quality_of_sleep, 40 as physical_activity_level, 7 as stress_level, 'Normal Weight' as bmi_category, 132 as blood_pressure_systolic, 87 as blood_pressure_diastolic, 80 as heart_rate, 4000 as daily_steps, 'Insomnia' as sleep_disorder
    union all select 20 as person_id, 'Male' as gender, 30 as age, 'Doctor' as occupation, 7.6 as sleep_duration, 7 as quality_of_sleep, 75 as physical_activity_level, 6 as stress_level, 'Normal' as bmi_category, 120 as blood_pressure_systolic, 80 as blood_pressure_diastolic, 70 as heart_rate, 8000 as daily_steps, 'None' as sleep_disorder
)
select gm.group_id,
    nid.person_id,
        decision_tree_predict(
            gm.decision_tree_model,
            array[cast(person_id as varchar),
                cast(gender as varchar),
                cast(age as varchar),
                cast(occupation as varchar),
                cast(sleep_duration as varchar),
                cast(quality_of_sleep as varchar),
                cast(physical_activity_level as varchar),
                cast(stress_level as varchar),
                cast(bmi_category as varchar),
                cast(blood_pressure_systolic as varchar),
                cast(blood_pressure_diastolic as varchar),
                cast(heart_rate as varchar),
                cast(daily_steps as varchar)]) as predicted_value
from model as gm
    cross join sleep_health_data as nid
order by gm.group_id, nid.person_id
limit 10000

Résultats de la requête et de l'analyse

Le champ predicted_value indique la classe à laquelle appartiennent les variables d'entrée dans input_variable_array.

group_id

person_id

predicted_value

Sleep Apnea

...