分類は、システムオブジェクトのクラスをオンラインで識別するために使用できる機械学習モデルです。 たとえば、モデルを使用して攻撃要求を識別できます。 モデルを使用して、要素間の関係を識別することもできます。 このトピックでは、分類分析関数の構文について説明します。 このトピックでは、関数の使用方法の例。
背景情報
分類分析関数のサンプルインデックスを次の図に示します。 詳細については、「インデックスの作成」をご参照ください。
次のコードは、サンプルログを示しています。
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
関数
分類の機械学習モデルを使用して、システムオブジェクトのクラスをオンラインで識別できます。
関数 | 構文 | 説明 | 戻り値のデータ型 |
decision_tree_classifier ( target_variable varchar, input_variable_array (varchar) 、 target_variable_name varchar, input_variable_name_array配列 (varchar) 、 input_variable_type_array配列 (varchar) 、 <オプション> model_options varchar ) | 最近指定したサンプルに基づいて、データの分類と原因分析に使用できるトレーニング済みの決定木モデルを返します。 | varchar | |
decision_tree_predict ( decision_tree_model_in_json varchar、 input_variable_array配列 (varchar) ) | 指定されたサンプルとdecision_tree_classifier関数によって返される決定木モデルに基づいて、システムオブジェクトのクラスを識別します。 | varchar |
decision_tree_classifier関数
decision_tree_classifier関数は、最近指定されたサンプルに基づくデータ分類および原因分析に使用できるトレーニング済みの決定木モデルを返します。
varchar decision_tree_classifier(target_variable varchar,input_variable_array (varchar),target_variable_name varchar,input_variable_name_array (varchar),input_variable_type_array (varchar),<オプション> model_options varchar)パラメーター | 説明 |
| 出力変数。 |
| 入力変数の配列。 この関数は、入力変数を文字列型に変換し、1次元配列を形成します。 |
| 出力変数の名前。 |
| 入力変数名の配列。 |
| 入力変数型の配列。 サポートされる入力変数型:
|
| 決定木モデルの高度なパラメーター。 ほとんどの場合、このパラメーターを設定する必要はありません。 値はキーと値のペアで構成されます。 複数のキーと値のペアは、コンマ (,) またはセミコロン (:) で区切ります。 たとえば、 決定木モデルの高度なパラメータ:
|
例
クエリ文
* | 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 the group_id field to specify that an aggregate function is returned in the decision tree model-based identification. 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クエリおよび分析の結果
sleep_health_modelフィールドは、決定木モデルを示す。decisionTreeEncodeフィールドは、デシジョンツリーモデルのシリアル化結果を示します。 この関数は、システムオブジェクトのクラスを識別するためにdecision_tree_predict関数で使用できる決定木モデルを返します。グループ_id
sleep_health_モデル
G1
{ "returnCode": 0, "message": "OK", "decisionTreeEncode": "gANjc2tsZWFybi50cmVlLl9jbGFzc2VzCkRlY2lzaW9uVHJlZUNsYXNzaWZpZXIKcQApgXEBfXECKFgJAAAAY3JpdGVyaW9ucQNYBAAAAGdpbmlxBFgIAAAAc3BsaXR0ZXJxBVgEAAAAYmVzdHEGWAkAAABtYXhfZGVwdGhxB05YEQAAAG1pbl9zYW1wbGVzX3NwbGl0cQhLFFgQAAAAbWluX3NhbXBsZXNfbGVhZnEJSwpYGAAAAG1pbl93ZWlnaHRfZnJhY3Rpb25fbGVhZnEKRwAAAAAAAAAAWAwAAABtYXhfZmVhdHVyZXNxC05YDgAAAG1heF9sZWFmX25vZGVzcQxOWAwAAAByYW5kb21fc3RhdGVxDU5YFQAAAG1pbl9pbXB1cml0eV9kZWNyZWFzZXEORz+EeuFHrhR7WBIAAABtaW5faW1wdXJpdHlfc3BsaXRxD05YDAAAAGNsYXNzX3dlaWdodHEQWAgAAABiYWxhbmNlZHERWAkAAABjY3BfYWxwaGFxEkcAAAAAAAAAAFgOAAAAbl9mZWF0dXJlc19pbl9xE0sXWAsAAABuX2ZlYXR1cmVzX3EUSxdYCgAAAG5fb3V0cHV0c19xFUsBWAgAAABjbGFzc2VzX3EWY251bXB5LmNvcmUubXVsdGlhcnJheQpfcmVjb25zdHJ1Y3QKcRdjbnVtcHkKbmRhcnJheQpxGEsAhXEZQwFicRqHcRtScRwoSwFLA4VxHWNudW1weQpkdHlwZQpxHlgDAAAAVTExcR+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" ] } }G2
{ "returnCode": 0, "message": "OK", "decisionTreeEncode": "gANjc2tsZWFybi50cmVlLl9jbGFzc2VzCkRlY2lzaW9uVHJlZUNsYXNzaWZpZXIKcQApgXEBfXECKFgJAAAAY3JpdGVyaW9ucQNYBAAAAGdpbmlxBFgIAAAAc3BsaXR0ZXJxBVgEAAAAYmVzdHEGWAkAAABtYXhfZGVwdGhxB05YEQAAAG1pbl9zYW1wbGVzX3NwbGl0cQhLFFgQAAAAbWluX3NhbXBsZXNfbGVhZnEJSwpYGAAAAG1pbl93ZWlnaHRfZnJhY3Rpb25fbGVhZnEKRwAAAAAAAAAAWAwAAABtYXhfZmVhdHVyZXNxC05YDgAAAG1heF9sZWFmX25vZGVzcQxOWAwAAAByYW5kb21fc3RhdGVxDU5YFQAAAG1pbl9pbXB1cml0eV9kZWNyZWFzZXEORz+EeuFHrhR7WBIAAABtaW5faW1wdXJpdHlfc3BsaXRxD05YDAAAAGNsYXNzX3dlaWdodHEQWAgAAABiYWxhbmNlZHERWAkAAABjY3BfYWxwaGFxEkcAAAAAAAAAAFgOAAAAbl9mZWF0dXJlc19pbl9xE0sXWAsAAABuX2ZlYXR1cmVzX3EUSxdYCgAAAG5fb3V0cHV0c19xFUsBWAgAAABjbGFzc2VzX3EWY251bXB5LmNvcmUubXVsdGlhcnJheQpfcmVjb25zdHJ1Y3QKcRdjbnVtcHkKbmRhcnJheQpxGEsAhXEZQwFicRqHcRtScRwoSwFLA4VxHWNudW1weQpkdHlwZQpxHlgDAAAAVTExcR+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" ] } }
decision_tree_predict関数
decision_tree_predict関数は、指定されたサンプルと返される決定木モデルに基づいて、システムオブジェクトのクラスを識別します。
varchar decision_tree_predict(decision_tree_model_in_json varchar,input_variable_array array(varchar))パラメーター | 説明 |
| |
| 分類で使用される入力変数の配列。 関数は入力変数を変換し、1次元配列を形成します。 |
例
クエリ文
* | 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クエリおよび分析の結果
predicted_valueフィールドは、input_variable_arrayパラメーターで指定された入力変数が属するクラスを示します。グループ_id
person_id
predicted_value
G1
4
睡眠時無呼吸
G1
5
睡眠時無呼吸
G1
6
睡眠時無呼吸
...
...
...