public static void Get_StringLength(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string sql_cmd_str = $@"
SELECT
CAST(MIN(K.[LEN]) AS BIGINT)
, CAST(AVG(K.[LEN]) AS DECIMAL)
, CAST(MAX(K.[LEN]) AS BIGINT) FROM (
SELECT LEN(V.A_T) [LEN] FROM (
SELECT CONVERT(NVARCHAR,T.[{_Ret.AttributeName}]) [A_T] FROM {_Ret.Relation} T) V) K;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
long? sl_min = _DR.IsDBNull(0) ? (long?)null : _DR.GetInt64(0);
decimal?sl_avg = _DR.IsDBNull(1) ? (decimal?)null : _DR.GetDecimal(1);
long? sl_max = _DR.IsDBNull(2) ? (long?)null : _DR.GetInt64(2);
_DR.Close();
if (!sl_min.HasValue || !sl_avg.HasValue || !sl_max.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.StringLength_Min = sl_min;
_Ret.StringLength_Avg = sl_avg;
_Ret.StringLength_Max = sl_max;
}
private static void Anly_Werteverteilung(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Anly_Werteverteilung" + "()..." + " ");
string sql_cmd_str = $@"SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' = '{relation}' AND COLUMN_NAME = '{attribut}'";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
string datatype = dr.GetString(0);
dr.Close();
if (NUM_Casttable.Keys.Contains(datatype))
{
Stat(ret, relation, attribut, datatype);
Frequenzdiagramm(ret, relation, attribut);
Median(ret, relation, attribut, datatype);
Benfordsche(ret, relation, attribut);
}
else
{
Stat_Text(ret, relation, attribut);
}
Modalwert(ret, relation, attribut);
Modalwert_Tot(ret, relation, attribut);
Modalwert_Anteilsm(ret, relation, attribut);
Standardwert(ret, relation, attribut);
}
public static Dictionary <string, List <string> > Remove_MinMax(Dictionary <string, List <string> > pairs, AErgAttribut aErgAttribut)
{
Dictionary <string, List <string> > _Ret = new Dictionary <string, List <string> >();
AErgAttribut _test = null;
bool a, b;
foreach (string _r in pairs.Keys)
{
foreach (string _a in pairs[_r])
{
_test = DPAnalysis.AttributAnalyse_Results_Sort[_r][_a];
if (_test.Datatype_Primitive == "NUM")
{
a = double.Parse(aErgAttribut.Statistics_Min) <= double.Parse(_test.Statistics_Min);
b = double.Parse(aErgAttribut.Statistics_Max) >= double.Parse(_test.Statistics_Max);
if (a && b)
{
if (_Ret.Keys.Contains(_r))
{
_Ret[_r].Add(_a);
}
else
{
_Ret.Add(_r, new List <string>(new string[] { _a }));
}
}
}
else
{
}
}
}
return(_Ret);
}
private static void Get_Histogramm_Select(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string conv = _Ret.Datatype_Documented == "float" || _Ret.Datatype_Documented == "real" ?
$"FORMAT(T.[{_Ret.AttributeName}], '0.0####################')" :
$"CONVERT(NVARCHAR,T.[{_Ret.AttributeName}])";
string sql_cmd_str = $@"
SELECT { conv }, COUNT_BIG(*) FROM {_Ret.Relation} T
GROUP BY T.[{_Ret.AttributeName}] ORDER BY T.[{_Ret.AttributeName}];
";
Dictionary <string, long> hg = new Dictionary <string, long>();
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
while (_DR.Read())
{
hg.Add(_DR.IsDBNull(0) ? "NULL" : _DR.GetString(0), _DR.GetInt64(1));
}
_DR.Close();
_Ret.Histogramm = hg;
}
public static void Get_Count(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
if (_Ret.Empty_Allowed.Value)
{
string sql_cmd_str = $@"
SELECT COUNT_BIG(T.[{_Ret.AttributeName}]) [C_A] FROM {_Ret.Relation} T;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
long?ca_tot = _DR.IsDBNull(0) ? (long?)null : _DR.GetInt64(0);
_DR.Close();
if (!ca_tot.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.Count_Attribute = ca_tot;
_Ret.Count_Attribute_Relative = ((decimal)ca_tot) / ((decimal)_Ret.Count_Rows);
_Ret.Count_Empty = _Ret.Count_Rows - _Ret.Count_Attribute;
_Ret.Count_Empty_Relative = ((decimal)_Ret.Count_Empty) / ((decimal)_Ret.Count_Rows);
}
else
{
_Ret.Count_Attribute = _Ret.Count_Rows;
_Ret.Count_Attribute_Relative = (decimal)1.0;
}
}
public static void Get_Count_Distinct(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string sql_cmd_str = $@"
SELECT COUNT_BIG(*) [C] FROM (
SELECT DISTINCT T.[{_Ret.AttributeName}] FROM { _Ret.Relation} T) V;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
long?cd_tot = _DR.IsDBNull(0) ? (long?)null : _DR.GetInt64(0);
_DR.Close();
if (!cd_tot.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.Count_Distinct = cd_tot.Value;
_Ret.Count_Distinct_Relative = ((decimal)cd_tot.Value) / ((decimal)_Ret.Count_Rows.Value);
}
public static AErgAttribut Analysieren(string relation, string attribut, int ordinal)
{
AErgAttribut ret = new AErgAttribut();
ret.Datenbank = DPAnalyse.DBName;
ret.AusgabePfad = $"{relation}";
ret.Titel = $"Attribut {ordinal} - [{attribut}]";
Anzahl(ret, relation, attribut);
if (ret.Anzahl > 0)
{
if (AttributHasContent(relation, attribut))
{
Anly_Kardinalität(ret, relation, attribut);
Anly_Werteverteilung(ret, relation, attribut);
Anly_Datenmuster(ret, relation, attribut);
}
else
{
FehlendeWerte(ret, relation, attribut);
FehlendeWerte_Anteilsm(ret, relation, attribut);
}
}
return(ret);
}
private static void TrimTrailBetroffene(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "TrimTrailBetroffene" + "()..." + " ");
string sql_cmd_str = $@"SELECT
SUM(V.LTRIM) AS [LTRIM_TOT]
,SUM(V.RTRIM) AS [RTRIM_TOT]
,CAST(SUM(V.LTRIM) AS DECIMAL) / CAST((SELECT COUNT_BIG(*) FROM {relation} T) AS DECIMAL) AS [LTRIM]
,CAST(SUM(V.RTRIM) AS DECIMAL) / CAST((SELECT COUNT_BIG(*) FROM {relation} T) AS DECIMAL) AS [RTRIM]
FROM
(SELECT
CAST(CASE WHEN T.[{attribut}] = LTRIM(T.[{attribut}]) THEN 0 ELSE 1 END AS BIGINT) AS [LTRIM]
,CAST(CASE WHEN T.[{attribut}] = RTRIM(T.[{attribut}]) THEN 0 ELSE 1 END AS BIGINT) AS [RTRIM]
FROM {relation} T) V;";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.TrimBetroffene = dr.GetInt64(0);
ret.TrailBetroffene = dr.GetInt64(1);
ret.TrimBetroffene_Anteilsm = dr.GetDecimal(2);
ret.TrailBetroffene_Anteilsm = dr.GetDecimal(3);
dr.Close();
}
private static void SucheDomäne(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "SucheDomäne" + "()..." + " ");
int domänenLimit = 25;
string sql_cmd_str = $@"IF (SELECT TOP 1 COUNT_BIG(*) FROM (SELECT DISTINCT T.[{attribut}] FROM {relation} T) V) <= {domänenLimit}
SELECT DISTINCT CAST(T.[{attribut}] AS NVARCHAR) FROM {relation} T ORDER BY CAST(T.[{attribut}] AS NVARCHAR) ASC;
ELSE
SELECT NULL;";
List <string> _domäne = new List <string>();
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
string _tmp = null;
while (dr.Read())
{
_tmp = dr.IsDBNull(0) ? null : dr.GetString(0);
if (_tmp == null)
{
ret.Domäne = null;
break;
}
_domäne.Add(_tmp);
}
dr.Close();
ret.Domäne = _domäne;
}
public static void Get_SimpleDomain(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string sql_cmd_str = $@"
SELECT CONVERT(NVARCHAR,K.A) FROM ( SELECT DISTINCT T.[{_Ret.AttributeName}] AS [A] FROM {_Ret.Relation} T ) K ORDER BY K.A;
";
List <string> sd = new List <string>();
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
while (_DR.Read())
{
sd.Add(_DR.IsDBNull(0) ? "NULL" : _DR.GetString(0));
}
_DR.Close();
if (sd.Count == 0)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.SimpleDomain = sd;
}
/*
* public string ModeValue { get; set; } = null;
* public long? ModeValue_Total { get; set; } = null;
* public decimal? ModeValue_Relative { get; set; } = null;
*/
public static void Get_ModeValue(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string sql_cmd_str = $@"
SELECT TOP 1 CONVERT(NVARCHAR, T.[{_Ret.AttributeName}]), COUNT_BIG(*) FROM {_Ret.Relation} T GROUP BY T.[{_Ret.AttributeName}];
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
string mv = _DR.IsDBNull(0) ? "NULL" : _DR.GetString(0);
long? mv_tot = _DR.IsDBNull(1) ? (long?)null : _DR.GetInt64(1);
_DR.Close();
if (!mv_tot.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.ModeValue = mv;
_Ret.ModeValue_Total = mv_tot;
_Ret.ModeValue_Relative = ((decimal)(mv_tot.Value)) / ((decimal)(_Ret.Count_Rows.Value));
}
public static void Get_Text_Order(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string sql_cmd_str = $@"
SELECT TOP 1
CONVERT(NVARCHAR,FIRST_VALUE(K.C) OVER (ORDER BY K.C ASC) )
, CONVERT(NVARCHAR,FIRST_VALUE(K.C) OVER (ORDER BY K.C DESC))
FROM (
SELECT
CAST(T.[{_Ret.AttributeName}] AS {_Ret.Datatype_Documented}) [C]
FROM {_Ret.Relation} T
) K;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
string t_a = _DR.IsDBNull(0) ? null : _DR.GetString(0);
string t_d = _DR.IsDBNull(1) ? null : _DR.GetString(1);
_DR.Close();
if (t_a == null && t_d == null)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.Text_First = t_a;
_Ret.Text_Last = t_d;
}
public static void Get_Benford(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
Dictionary <string, long> ben = new Dictionary <string, long>();
string sql_cmd_str = $@"
SELECT V.LEADCHAR, COUNT_BIG(*) FROM
(SELECT LEFT(CONVERT(NVARCHAR,T.[{_Ret.AttributeName}]), 1) AS [LEADCHAR] FROM {_Ret.Relation} T) V
GROUP BY V.LEADCHAR ORDER BY V.LEADCHAR ASC;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
while (_DR.Read())
{
ben.Add(_DR.IsDBNull(0) ? "NULL" : _DR.GetString(0), _DR.IsDBNull(1) ? -1 : _DR.GetInt64(1));
}
_DR.Close();
if (ben.Values.Contains(-1))
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
_Ret.Benford = ben;
}
private static void Median(AErgAttribut ret, string relation, string attribut, string datatype)
{
Console.Write("\r" + "Median" + "()..." + " ");
string sql_cmd_str = $@"DECLARE @c BIGINT = (SELECT COUNT(T.[{attribut}]) FROM {relation} T);
SELECT CAST(AVG(1.0 * val) AS DECIMAL) AS [MEDIAN]
FROM (
SELECT CAST(T.[{attribut}] AS {datatype}) val FROM {relation} T
ORDER BY val
OFFSET (@c - 1) / 2 ROWS
FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
if (dr.IsDBNull(0))
{
ret.Median = null;
}
else
{
ret.Median = dr.GetDecimal(0);
}
dr.Close();
}
private static void Anly_Datenmuster(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Anly_Datenmuster" + "()..." + " ");
Datentyp_PrimitivUndKonversion(ret, relation, attribut);
SucheDomäne(ret, relation, attribut);
}
private static void Datentyp_PrimitivUndKonversion(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Datentyp_PrimitivUndKonversion" + "()..." + " ");
string sql_cmd_str = $@"SELECT CAST(DATA_TYPE AS NVARCHAR) FROM INFORMATION_SCHEMA.COLUMNS
WHERE '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' = '{relation}' AND COLUMN_NAME = '{attribut}'";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
string datatype_actual = dr.GetString(0);
dr.Close();
ret.DokumentierterDatentyp = datatype_actual;
if (PrimitiveKonversion.Keys.Contains(datatype_actual))
{
string konversion = PrimitiveKonversion[datatype_actual];
if (konversion == null)
{
ret.PrimitiverDatentyp = "TEXT";
PrimitiverDatentypEigenschaften(ret, relation, attribut);
if (ret.PrimitiverDatentypEigenschaften != null)
{
if (ret.PrimitiverDatentypEigenschaften.Keys.Contains("NUM"))
{
if (ret.PrimitiverDatentypEigenschaften["NUM"] > 0)
{
DatentypVonText(ret, relation, attribut, datatype_actual);
}
}
}
TrimTrailBetroffene(ret, relation, attribut);
}
else
{
ret.PrimitiverDatentyp = konversion;
ret.PrimitiverDatentypEigenschaften_Anteilsm = null;
ret.DatentypenKonversion = null;
ret.DatentypenKonversion_Anteilsm = null;
//TODO IF NUM LÄNGE & PRÄZISIONSTEST
}
}
else
{
ret.PrimitiverDatentyp = "OTHER";
ret.PrimitiverDatentypEigenschaften_Anteilsm = null;
ret.DatentypenKonversion = null;
ret.DatentypenKonversion_Anteilsm = null;
}
}
private static void Anly_Kardinalität(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Anly_Kardinalität" + "()..." + " ");
FehlendeWerte(ret, relation, attribut);
FehlendeWerte_Anteilsm(ret, relation, attribut);
Einzigartigkeit(ret, relation, attribut);
Einzigartigkeit_Aneilsm(ret, relation, attribut);
Zeichenlänge(ret, relation, attribut);
}
public static void Get_Histogramm(AErgAttribut _Ret)
{
if (_Ret.Count_Distinct <= DPAttribut.DOMAIN_SIMPLE_BORDER)
{
Get_Histogramm_Select(_Ret);
}
else
{
Get_Histogramm_Calculated(_Ret);
}
}
private static void Anzahl(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Anzahl" + "()..." + " ");
string sql_cmd_str = $@"SELECT COUNT_BIG(*) FROM {relation} T";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Anzahl = dr.GetInt64(0);
dr.Close();
}
private static void FehlendeWerte(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "FehlendeWerte" + "()..." + " ");
string sql_cmd_str = $@"SELECT COUNT_BIG(*) - COUNT_BIG(T.[{attribut}]) FROM {relation} T";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.FehlendeWerte = dr.GetInt64(0);
dr.Close();
}
private static void Einzigartigkeit(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Einzigartigkeit" + "()..." + " ");
string sql_cmd_str = $@"SELECT COUNT_BIG(*) FROM (SELECT DISTINCT T.[{attribut}] FROM {relation} T) V";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Einzigartigkeit = dr.GetInt64(0);
dr.Close();
}
private static void Modalwert_Tot(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Modalwert_Tot" + "()..." + " ");
string sql_cmd_str = $@"SELECT TOP 1 COUNT_BIG(*) FROM {relation} T GROUP BY T.[{attribut}] ORDER BY COUNT(*) DESC;";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Modalwert_Tot = dr.GetInt64(0);
dr.Close();
}
private static void Modalwert(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Modalwert" + "()..." + " ");
string sql_cmd_str = $@"SELECT TOP 1 CAST(T.[{attribut}] AS NVARCHAR) FROM {relation} T GROUP BY T.[{attribut}] ORDER BY COUNT_BIG(*) DESC;";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Modalwert = dr.IsDBNull(0) ? null : dr.GetString(0);
dr.Close();
}
private static void FehlendeWerte_Anteilsm(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "FehlendeWerte_Anteilsm" + "()..." + " ");
string sql_cmd_str = $@"SELECT
CAST((SELECT COUNT_BIG(*) - COUNT_BIG(T.[{attribut}]) FROM {relation} T) AS DECIMAL) /
CAST((SELECT COUNT_BIG(*) FROM {relation} T) AS DECIMAL)";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.FehlendeWerte_Anteilsm = dr.GetDecimal(0);
dr.Close();
}
private static void Einzigartigkeit_Aneilsm(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Einzigartigkeit_Aneilsm" + "()..." + " ");
string sql_cmd_str = $@"SELECT
CAST((SELECT COUNT_BIG(*) FROM (SELECT DISTINCT T.[{attribut}] FROM {relation} T) V) AS DECIMAL) /
CAST((SELECT COUNT_BIG(*) FROM {relation} T) AS DECIMAL)";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Einzigartigkeit_Anteilsm = dr.GetDecimal(0);
dr.Close();
}
private static void Standardwert(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Standardwert" + "()..." + " ");
string sql_cmd_str = $@"SELECT CAST(COLUMN_DEFAULT AS NVARCHAR) FROM INFORMATION_SCHEMA.COLUMNS
WHERE '['+TABLE_SCHEMA+'].['+TABLE_NAME + ']' = '{relation}' AND COLUMN_NAME = '{attribut}';";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Standardwert = dr.IsDBNull(0) ? null : dr.GetString(0);
dr.Close();
}
public static void Get_PrimitiveDatatypeProperties(AErgAttribut _Ret)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string sql_cmd_str = $@"
DECLARE @true BIGINT = 1;
DECLARE @false BIGINT = 0;
SELECT
SUM(V.NUM) AS [NUM_TOT]
, SUM(V.APH) AS [APH_TOT]
, SUM(V.OTH) AS [OTH_TOT]
FROM
(SELECT
CASE WHEN T.[{_Ret.AttributeName}] LIKE '%[0-9]%' THEN @true ELSE @false END [NUM]
,CASE WHEN T.[{_Ret.AttributeName}] LIKE '%[a-zA-Z]%' THEN @true ELSE @false END [APH]
,CASE WHEN T.[{_Ret.AttributeName}] LIKE '%[^0-9a-zA-Z]%' THEN @true ELSE @false END [OTH]
FROM {_Ret.Relation} T) V;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
long?prp_aph = _DR.IsDBNull(0) ? (long?)null : _DR.GetInt64(0);
long?prp_num = _DR.IsDBNull(1) ? (long?)null : _DR.GetInt64(1);
long?prp_oth = _DR.IsDBNull(2) ? (long?)null : _DR.GetInt64(2);
_DR.Close();
if (!prp_aph.HasValue || !prp_num.HasValue || !prp_oth.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
Dictionary <string, long> prp_tot = new Dictionary <string, long>();
prp_tot.Add("NUM", prp_aph.Value);
prp_tot.Add("APH", prp_num.Value);
prp_tot.Add("OTH", prp_oth.Value);
Dictionary <string, decimal> prp_rel = new Dictionary <string, decimal>();
prp_rel.Add("NUM", ((decimal)prp_tot["NUM"]) / ((decimal)_Ret.Count_Attribute.Value));
prp_rel.Add("APH", ((decimal)prp_tot["APH"]) / ((decimal)_Ret.Count_Attribute.Value));
prp_rel.Add("OTH", ((decimal)prp_tot["OTH"]) / ((decimal)_Ret.Count_Attribute.Value));
_Ret.Datatype_Primitive_Properties = prp_tot;
_Ret.Datatype_Primitive_Properties_Relative = prp_rel;
}
private static void Zeichenlänge(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Zeichenlänge" + "()..." + " ");
string sql_cmd_str = $@"SELECT CAST(MIN(V.STRLEN) AS BIGINT) AS [MIN] ,CAST(MAX(V.STRLEN) AS BIGINT) AS [MAX] ,CAST(AVG(V.STRLEN) AS DECIMAL) AS [AVG]
FROM (SELECT LEN(CAST(T.[{attribut}] AS VARCHAR)) AS STRLEN FROM {relation} T) V";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Zeichenlänge_Min = dr.IsDBNull(0) ? 0 : dr.GetInt64(0);
ret.Zeichenlänge_Max = dr.IsDBNull(1) ? 0 : dr.GetInt64(1);
ret.Zeichenlänge_Avg = dr.IsDBNull(2) ? 0 : dr.GetDecimal(2);
dr.Close();
}
private static void Stat_Text(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Stat_Text" + "()..." + " ");
string sql_cmd_str = $@"SELECT
CAST((SELECT TOP 1 T.[{attribut}] FROM {relation} T ORDER BY T.[{attribut}] ASC) AS NVARCHAR) AS [FIRST]
,CAST((SELECT TOP 1 T.[{attribut}] FROM {relation} T ORDER BY T.[{attribut}] DESC) AS NVARCHAR) AS [LAST];";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Text_Start = dr.IsDBNull(0) ? null : dr.GetString(0);
ret.Text_Ende = dr.IsDBNull(1) ? null : dr.GetString(1);
dr.Close();
}
private static void Modalwert_Anteilsm(AErgAttribut ret, string relation, string attribut)
{
Console.Write("\r" + "Modalwert_Anteilsm" + "()..." + " ");
string sql_cmd_str = $@"SELECT
(CAST((SELECT TOP 1 COUNT_BIG(*) FROM {relation} T GROUP BY T.[{attribut}] ORDER BY COUNT_BIG(*) DESC) AS DECIMAL)) /
(CAST((SELECT COUNT_BIG(T.[{attribut}]) FROM {relation} T) AS DECIMAL));";
SqlDataReader dr = DBManager.ExecuteRead(sql_cmd_str);
dr.Read();
ret.Modalwert_Anteilsm = dr.GetDecimal(0);
dr.Close();
}
