EXCEL のワークシートに挿入されたテーブルにはデフォルトでオートフィルターが設定されています．このテーブルに対してオートフィルターをかけた結果を VBA で取得する方法は難解で，従来の考え方とは少し異なります．
より抽象度の高い考え方をする必要があります．リレーショナルデータベースの概念である集合論を理解する必要があります．
タグ: SQL Server
How to create scalar function of SQL Server in order to define nPNA?
The Japanese Society for Dialysis Therapy (JSDT) recommends PCR as an indicator of protein intake. Otherwise K/DOQQI recommends nPNA. If you calculate Kt/V with Daugirdas’ method, you can also define nPNA.
Execute the following procedure.
CREATE FUNCTION Function_nPNA ( @preBUN DEC(4, 1) , @postBUN DEC(4, 1) , @preWeight DEC(4, 1) , @postWeight DEC(4, 1) , @DialysisDuration int ) RETURNS DEC(3, 2) AS BEGIN DECLARE @nPNA DEC(3, 2) SELECT @nPNA = @preBUN / (36.3 + 5.48 * (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration)) + 53.5 / (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration))) + 0.168 RETURN @nPNA END
Reference: Simplified nutritional screening tools for patients on maintenance hemodialysis
SQL Serverのスカラー値関数としてnPNAを定義するには
たんぱく質摂取量の指標として日本透析医学会では PCR を採用しています．一方 K/DOQQI では nPNA を採用しています．Daugirdas の方法で Kt/V を計算すると nPNA も計算できます．
下記プロシージャを実行して関数を作成します．
CREATE FUNCTION Function_nPNA ( @preBUN DEC(4, 1) , @postBUN DEC(4, 1) , @preWeight DEC(4, 1) , @postWeight DEC(4, 1) , @DialysisDuration int ) RETURNS DEC(3, 2) AS BEGIN DECLARE @nPNA DEC(3, 2) SELECT @nPNA = @preBUN / (36.3 + 5.48 * (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration)) + 53.5 / (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration))) + 0.168 RETURN @nPNA END
How to define Kt/V, an indicator of the efficiency of dialysis, as scalar function of SQL Server?
In Japan, Shinzato’s fomula for calculating Kt/V, an indicator of efficiency of dialysis, is recommended by JSDT. Since integral equation is used to solve Shinzato’s method, you couldn’t solve algebraically. In K/DOQQI, it is usual to solve Kt/V with Daugirdas’ method. Shinzato has described that Daugirdas’ Kt/V is similar to Shinzato’s Kt/V.
![\displaystyle \mathrm{Kt/V} = - LN( R - 0.08 \times t ) + \left[ 4 - \left( 3.5 \times R \right) \right] \times\frac{\mathrm{UF}}{\mathrm{W}}\\ = - LN \left( \frac{\mathrm{postBUN}}{\mathrm{preBUN}} - 0.008 \times t \right) + \left[ 4 - \left( 3.5 \times \frac{\mathrm{postBUN}}{\mathrm{preBUN}} \right) \right] \times \frac{\mathrm{preWeight} - \mathrm{postWeight}}{\mathrm{postWeight}} \cdots(1)](https://s0.wp.com/latex.php?latex=%5Cdisplaystyle+%5Cmathrm%7BKt%2FV%7D+%3D+-+LN%28+R+-+0.08+%5Ctimes+t+%29+%2B+%5Cleft%5B+4+-+%5Cleft%28+3.5+%5Ctimes+R+%5Cright%29+%5Cright%5D+%5Ctimes%5Cfrac%7B%5Cmathrm%7BUF%7D%7D%7B%5Cmathrm%7BW%7D%7D%5C%5C++%3D+-+LN+%5Cleft%28+%5Cfrac%7B%5Cmathrm%7BpostBUN%7D%7D%7B%5Cmathrm%7BpreBUN%7D%7D+-+0.008+%5Ctimes+t+%5Cright%29+%2B+%5Cleft%5B+4+-+%5Cleft%28+3.5+%5Ctimes+%5Cfrac%7B%5Cmathrm%7BpostBUN%7D%7D%7B%5Cmathrm%7BpreBUN%7D%7D+%5Cright%29+%5Cright%5D+%5Ctimes+%5Cfrac%7B%5Cmathrm%7BpreWeight%7D+-+%5Cmathrm%7BpostWeight%7D%7D%7B%5Cmathrm%7BpostWeight%7D%7D+%5Ccdots%281%29&bg=T&fg=000000&s=0 "\displaystyle \mathrm{Kt/V} = - LN( R - 0.08 \times t ) + \left[ 4 - \left( 3.5 \times R \right) \right] \times\frac{\mathrm{UF}}{\mathrm{W}}\\ = - LN \left( \frac{\mathrm{postBUN}}{\mathrm{preBUN}} - 0.008 \times t \right) + \left[ 4 - \left( 3.5 \times \frac{\mathrm{postBUN}}{\mathrm{preBUN}} \right) \right] \times \frac{\mathrm{preWeight} - \mathrm{postWeight}}{\mathrm{postWeight}} \cdots(1)")
![\displaystyle \mathrm{Ce} = \mathrm{Cs} Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) + \frac{\mathrm{G}}{\mathrm{K}}\left[ 1 - Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) \right] \cdots(3)](https://s0.wp.com/latex.php?latex=%5Cdisplaystyle+%5Cmathrm%7BCe%7D+%3D+%5Cmathrm%7BCs%7D+Exp%5Cleft%28+-+%5Cfrac%7B%5Cmathrm%7BKt%7D%7D%7B%5Cmathrm%7BV%7D%7D+%5Cright%29+%2B+%5Cfrac%7B%5Cmathrm%7BG%7D%7D%7B%5Cmathrm%7BK%7D%7D%5Cleft%5B+1+-+Exp%5Cleft%28+-+%5Cfrac%7B%5Cmathrm%7BKt%7D%7D%7B%5Cmathrm%7BV%7D%7D+%5Cright%29+%5Cright%5D+%5Ccdots%283%29&bg=T&fg=000000&s=0 "\displaystyle \mathrm{Ce} = \mathrm{Cs} Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) + \frac{\mathrm{G}}{\mathrm{K}}\left[ 1 - Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) \right] \cdots(3)")
Execute the procedure as following;
CREATE FUNCTION Function_KtV ( @preBUN DEC(4, 1) , @postBUN DEC(4, 1) , @preWeight DEC(4, 1) , @postWeight DEC(4, 1) , @DialysisDuration int ) RETURNS DEC(3,2) AS BEGIN DECLARE @KtV DEC(3, 2) SELECT @KtV = - LOG(@postBUN / @preBUN - 0.008 * @DialysisDuration / 60) + (4 - (3.5 * @postBUN / @preBUN)) * ((@preWeight - @postWeight) / @postWeight) RETURN @KtV END GO
References: JSDT 29 (12): 1511-1516, 1996
Second Generation Logarithmic Estimates of Single-Pool Variable Volume
SQL Serverのスカラー値関数としてKt/Vを定義するには
透析患者の透析効率を計算するには日本透析医学会の推奨する新里式があります．原著論文を見ると積分方程式を解く必要があり，一般的な数学の知識では歯が立ちません．ここでは Daugirdas による Kt/V をデータベース内で計算する方法を述べます．新里自身も Daugirdas による Kt/V と新里式による Kt/V とはかなり一致していると述べており，ほぼ代用可能ではないかと思われます．(1) が Daugirdas の方法で (2) と (3) を解くと新里式になります．
![\displaystyle \mathrm{Kt/V} = - LN( R - 0.008 \times t ) + \left[ 4 - \left( 3.5 \times R \right) \right] \times\frac{\mathrm{UF}}{\mathrm{W}}\\ = - LN \left( \frac{\mathrm{postBUN}}{\mathrm{preBUN}} - 0.008 \times t \right) + \left[ 4 - \left( 3.5 \times \frac{\mathrm{postBUN}}{\mathrm{preBUN}} \right) \right] \times \frac{\mathrm{preWeight} - \mathrm{postWeight}}{\mathrm{postWeight}} \cdots(1)](https://s0.wp.com/latex.php?latex=%5Cdisplaystyle+%5Cmathrm%7BKt%2FV%7D+%3D+-+LN%28+R+-+0.008+%5Ctimes+t+%29+%2B+%5Cleft%5B+4+-+%5Cleft%28+3.5+%5Ctimes+R+%5Cright%29+%5Cright%5D+%5Ctimes%5Cfrac%7B%5Cmathrm%7BUF%7D%7D%7B%5Cmathrm%7BW%7D%7D%5C%5C++%3D+-+LN+%5Cleft%28+%5Cfrac%7B%5Cmathrm%7BpostBUN%7D%7D%7B%5Cmathrm%7BpreBUN%7D%7D+-+0.008+%5Ctimes+t+%5Cright%29+%2B+%5Cleft%5B+4+-+%5Cleft%28+3.5+%5Ctimes+%5Cfrac%7B%5Cmathrm%7BpostBUN%7D%7D%7B%5Cmathrm%7BpreBUN%7D%7D+%5Cright%29+%5Cright%5D+%5Ctimes+%5Cfrac%7B%5Cmathrm%7BpreWeight%7D+-+%5Cmathrm%7BpostWeight%7D%7D%7B%5Cmathrm%7BpostWeight%7D%7D+%5Ccdots%281%29&bg=T&fg=000000&s=0 "\displaystyle \mathrm{Kt/V} = - LN( R - 0.008 \times t ) + \left[ 4 - \left( 3.5 \times R \right) \right] \times\frac{\mathrm{UF}}{\mathrm{W}}\\ = - LN \left( \frac{\mathrm{postBUN}}{\mathrm{preBUN}} - 0.008 \times t \right) + \left[ 4 - \left( 3.5 \times \frac{\mathrm{postBUN}}{\mathrm{preBUN}} \right) \right] \times \frac{\mathrm{preWeight} - \mathrm{postWeight}}{\mathrm{postWeight}} \cdots(1)")
下記プロシージャを実行して関数を作成します．
CREATE FUNCTION Function_KtV ( @preBUN DEC(4, 1) , @postBUN DEC(4, 1) , @preWeight DEC(4, 1) , @postWeight DEC(4, 1) , @DialysisDuration int ) RETURNS DEC(3,2) AS BEGIN DECLARE @KtV DEC(3, 2) SELECT @KtV = - LOG(@postBUN / @preBUN - 0.008 * @DialysisDuration / 60) + (4 - (3.5 * @postBUN / @preBUN)) * ((@preWeight - @postWeight) / @postWeight) RETURN @KtV END GO
参照：透析会誌 29 (12): 1511-1516, 1996
Second Generation Logarithmic Estimates of Single-Pool Variable Volume
How to create scalar function of SQL Server in order to calculate GNRI?
Malnutrition in elder people increases the risk of death. Geriatric Nutrition Risk Index (GNRI) is the tool to detect malnutrition easily in hemodialysis patients, too. The definition is as following;
where ideal body weight is given by multiplying 22 the square of height. But body weight should be replaced with ideal body weight if body weight is greater than ideal body weight. Then the second term is equal to 1.
Table is defined as following procedure. It is based on the survey list of the Japanese Society for Dialysis Therapy in 2013.
CREATE TABLE dbo.T_JSDT( ID nchar(8) NOT NULL, DATE_Survey date NOT NULL, Diabetes nchar(1) NOT NULL, Myocardial_Infarction nchar(1) NOT NULL, Cerebral_Hemorrhage nchar(1) NOT NULL, Cerebral_Infarction nchar(1) NOT NULL, Amputation nchar(1) NOT NULL, Femoral_Fracture nchar(1) NOT NULL, EPS nchar(1) NOT NULL, Hypertensive_Agents nchar(1) NOT NULL, Smoke nchar(1) NOT NULL, Therapy_Mode nchar(10) NOT NULL, Combination_PD nchar(1) NOT NULL, History_PD nchar(1) NOT NULL, Transplantation_COUNT nchar(1) NOT NULL, DIalysis_COUNT int NULL, Dialysis_Duration int NULL, QB int NULL, Height decimal(4, 1) NOT NULL, preWeight decimal(4, 1) NOT NULL, postWeight decimal(4, 1) NULL, preBUN decimal(4, 1) NOT NULL, postBUN decimal(4, 1) NULL, preCre decimal(5, 2) NOT NULL, postCre decimal(5, 2) NULL, Albumin decimal(3, 1) NOT NULL, CRP decimal(4, 2) NOT NULL, Ca decimal(3, 1) NOT NULL, IP decimal(3, 1) NOT NULL, Hemoglobin decimal(3, 1) NOT NULL, TIBC decimal(3, 0) NULL, Fe decimal(3, 0) NULL, Ferritin decimal(5, 1) NULL, TCHO decimal(3, 0) NOT NULL, HDLC decimal(3, 0) NOT NULL, PTH_mode nchar(1) NOT NULL, PTH decimal(4, 0) NULL, HbA1c decimal(3, 1) NULL, GA decimal(3, 1) NULL, SBP decimal(3, 0) NOT NULL, DBP decimal(3, 0) NOT NULL, HR decimal(3, 0) NOT NULL, KtV_JSDT decimal(3, 2) NULL, nPCR_JSDT decimal(3, 2) NULL, [%CRE] decimal(4, 1) NULL, CONSTRAINT PK_T_JSDT PRIMARY KEY (ID, DATE_Survey)
We need height, body weight and albumin in the table. Execute following procedure to create function.
CREATE FUNCTION Function_GNRI (@Albumin dec(3, 1), @Height dec(4, 1), @Weight dec(4, 1)) RETURNS DEC(5, 2) AS BEGIN DECLARE @GNRI DEC(5,2) SELECT @GNRI = 14.89 * @Albumin + 41.7 * CASE WHEN @Weight > ( 22 * POWER(@Height/100, 2)) THEN ( 22 * POWER(@Height/100, 2)) ELSE @Weight END / ( 22 * POWER(@Height/100, 2)) RETURN @GNRI END
Execute following query to calculate GNRI.
WITH CTE AS (SELECT J.ID AS ID , J.DATE_Survey AS DATE_Survey , J.Albumin AS ALB , J.Height AS Height , CASE WHEN J.postWeight IS NULL THEN J.preWeight ELSE J.postWeight END AS Weight FROM dbo.T_JSDT AS J ), CTE_GNRI AS (SELECT CTE.ID AS ID , CTE.DATE_Survey AS DATE_Survey , dbo.Function_GNRI(CTE.ALB, CTE.Height, CTE.Weight) AS GNRI FROM CTE) SELECT * FROM CTE_GNRI;
Reference: Simplified nutritional screening tools for patients on maintenance hemodialysis
SQL Serverのスカラー値関数としてGNRIを定義するには
高齢者における低栄養は死亡のリスクを高めることが知られています．Geriatric Nutrition Risk Index (GNRI) は低栄養を簡易に検出できるツールです．単に高齢者だけではなく，維持透析患者においても有用です．その定義は以下です．
ここで理想体重 (kg) は身長 (m) の二乗に 22 を乗じて得られます．ただし実体重が理想体重を上回る場合には，実体重を理想体重に置き換えます．つまり第 2 項の分数は 1 に等しくなります．
データベースのテーブル定義は以下の通りであるとします．日本透析医学会統計調査票の 2013 年版に基いています．
CREATE TABLE dbo.T_JSDT( ID nchar(8) NOT NULL, DATE_Survey date NOT NULL, Diabetes nchar(1) NOT NULL, Myocardial_Infarction nchar(1) NOT NULL, Cerebral_Hemorrhage nchar(1) NOT NULL, Cerebral_Infarction nchar(1) NOT NULL, Amputation nchar(1) NOT NULL, Femoral_Fracture nchar(1) NOT NULL, EPS nchar(1) NOT NULL, Hypertensive_Agents nchar(1) NOT NULL, Smoke nchar(1) NOT NULL, Therapy_Mode nchar(10) NOT NULL, Combination_PD nchar(1) NOT NULL, History_PD nchar(1) NOT NULL, Transplantation_COUNT nchar(1) NOT NULL, DIalysis_COUNT int NULL, Dialysis_Duration int NULL, QB int NULL, Height decimal(4, 1) NOT NULL, preWeight decimal(4, 1) NOT NULL, postWeight decimal(4, 1) NULL, preBUN decimal(4, 1) NOT NULL, postBUN decimal(4, 1) NULL, preCre decimal(5, 2) NOT NULL, postCre decimal(5, 2) NULL, Albumin decimal(3, 1) NOT NULL, CRP decimal(4, 2) NOT NULL, Ca decimal(3, 1) NOT NULL, IP decimal(3, 1) NOT NULL, Hemoglobin decimal(3, 1) NOT NULL, TIBC decimal(3, 0) NULL, Fe decimal(3, 0) NULL, Ferritin decimal(5, 1) NULL, TCHO decimal(3, 0) NOT NULL, HDLC decimal(3, 0) NOT NULL, PTH_mode nchar(1) NOT NULL, PTH decimal(4, 0) NULL, HbA1c decimal(3, 1) NULL, GA decimal(3, 1) NULL, SBP decimal(3, 0) NOT NULL, DBP decimal(3, 0) NOT NULL, HR decimal(3, 0) NOT NULL, KtV_JSDT decimal(3, 2) NULL, nPCR_JSDT decimal(3, 2) NULL, [%CRE] decimal(4, 1) NULL, CONSTRAINT PK_T_JSDT PRIMARY KEY (ID, DATE_Survey)
上記テーブルの中で必要な項目は身長，体重，アルブミン値です．下記プロシージャを実行して関数を作成します．実体重が理想体重を上回る場合には実体重を理想体重に置き換えるという条件は CASE 式の中で評価します．
CREATE FUNCTION Function_GNRI (@Albumin dec(3, 1), @Height dec(4, 1), @Weight dec(4, 1)) RETURNS DEC(5, 2) AS BEGIN DECLARE @GNRI DEC(5,2) SELECT @GNRI = 14.89 * @Albumin + 41.7 * CASE WHEN @Weight > ( 22 * POWER(@Height/100, 2)) THEN ( 22 * POWER(@Height/100, 2)) ELSE @Weight END / ( 22 * POWER(@Height/100, 2)) RETURN @GNRI END
下記のクエリを実行して GNRI を求めます．CASE 式の中身は透析後体重が空欄の場合は透析前体重で代用するという意味です．
WITH CTE AS (SELECT J.ID AS ID , J.DATE_Survey AS DATE_Survey , J.Albumin AS ALB , J.Height AS Height , CASE WHEN J.postWeight IS NULL THEN J.preWeight ELSE J.postWeight END AS Weight FROM dbo.T_JSDT AS J ), CTE_GNRI AS (SELECT CTE.ID AS ID , CTE.DATE_Survey AS DATE_Survey , dbo.Function_GNRI(CTE.ALB, CTE.Height, CTE.Weight) AS GNRI FROM CTE) SELECT * FROM CTE_GNRI;