SQL для создания отчетов и выполнения анализа в файловых базах геоданных

Файловая база геоданных предоставляет дополнительные возможности анализа и отчетности за счет использования выражений и псевдонимов в методе SubFields (список полей) в QueryDef (определении запроса). Кроме того, имеется поддержка ORDER BY и GROUP BY в PostFixClause. Для поддержки GROUP BY выражения включают такие функции агрегирования, как MIN, MAX и SUM.

Ниже приведен список функций, которые могут использоваться с файловыми базами геоданных и в примерах.

ALIAS

Используется для присвоения столбцу другого, более понятного имени.

SQL

SELECT NAME, POP1997 - POP1990 as PopChange
FROM counties ORDER BY NAME

ArcObjects

pQueryDef.SubFields = "NAME, POP1997 - POP1990 as PopChange"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY NAME"

CASE – автоматизированное проектирование и создание программ

Оценивает список условий и возвращает одно из выходных выражений.

SQL

SELECT name,salary,
CASE
	WHEN salary <= 2000 THEN 'low'
	WHEN salary > 2000 AND salary <= 3000 THEN 'average'
	WHEN salary > 3000 THEN 'high'
	END AS salary_level
	FROM employees
	ORDER BY salary ASC

ArcObjects

pQueryDef.SubFields = "name,salary,
CASE
	WHEN salary <= 2000 THEN 'low'
	WHEN salary > 2000 AND salary <= 3000 THEN 'average'
	WHEN salary > 3000 THEN 'high'
	END AS salary_level"
	pQueryDef.Tables = " employees"
	pQueryDef.PostfixClause = " ORDER BY salary ASC "

COALESCE

Возвращает в своих аргументах первое непустое значение поля.

SQL

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone)
Contact_Phone
FROM Contact_Info

ArcObjects

queryDef.SubFields = "Name, COALESCE(Business_Phone, Cell_Phone,
Home_Phone) Contact_Phone"
queryDef.Tables = "Contact_Info"

Contact_Info:

Название	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

Результат:

Название	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

EXPRESSIONS

SQL

SELECT SIN(sunangle)
FROM sightings

ArcObjects

queryDef.SubFields = "SIN(sunangle)"
queryDef.Tables = "sightings"

SET FUNCTIONS

Оператор	Описание
AVG	Вычисляет среднее всех значений в группе. Пустые (null) значения не учитываются.
COUNT(*), COUNT(выражение)	COUNT(*) возвращает число записей в таблице. Пустые (null) значения учитываются. COUNT(выражение) возвращает число значений в данном выражении. Пустые (null) значения не учитываются.
MAX	Находит максимальные значения в группе. Пустые (null) значения не учитываются.
MIN	Находит минимальное значение в группе. Пустые (null) значения не учитываются.
STDDEV, STDDEV_SAMP	Возвращает выборочное стандартное отклонение выражения.
STDDEV_POP	Возвращает стандартное отклонение совокупности выражения.
SUM	Находит значения в группе. Пустые (null) значения не учитываются.
VAR, VAR_SAMP	Возвращает выборочный разброс выражения.
VAR_POP	Возвращает общий разброс выражения.

ARITHMETIC OPERATIORS (Математические операторы)

Для сложения, вычитания, умножения и деления числовых значений можно использовать арифметические операторы.

Оператор	Описание
*	Арифметический оператор умножения
/	Арифметический оператор деления
+	Арифметический оператор сложения
-	Арифметический оператор вычитания

ФУНКЦИИ

Ниже приведен полный список функций, поддерживаемых файловыми базами геоданных.

Функции работы с датами

Функция	Описание
CURRENT_DATE	Возвращает текущую дату.
EXTRACT (extract_field FROM extract_source)	Возвращает элемент extract_field из extract_source. Аргумент extract_source является выражением даты–времени. Аргументом extract_field может быть одно из следующих ключевых слов: YEAR, MONTH, DAY, HOUR, MINUTE или SECOND.
CURRENT TIME	Возвращает текущую дату.
CURRENT_TIMESTAMP	Возвращает текущие время и дату.

Строковые функции

Аргументы, обозначаемые string_exp, могут быть названием столбца, строковой константой или результатом другой скалярной функции, где исходные данные могут быть представлены в виде символов.

Аргументы, обозначаемые character_exp, являются строками символов переменной длины.

Аргументы, обозначаемые start или length , могут быть числовыми константами или результатом другой скалярной функции, где исходные данные могут быть представлены в виде символов.

Строковые функции, перечисленные здесь, базируются на 1; то есть, первым символом в строке является символ 1.

Функция	Описание
CHAR_LENGTH (string_exp)	Возвращает длину строкового выражения в символах.
CONCAT(string_exp1, string_exp2)	Возвращает символьную строку, являющуюся результатом присоединения string_exp2 к string_exp1.
LOWER(string_exp)	Возвращает строку, идентичную string_exp, в которой все символы верхнего регистра изменены на символы нижнего регистра.
POSITION(character_exp IN character_exp)	Возвращает место первого символьного выражения во втором символьном выражении. Результат – число с точностью, определяемой реализацией и коэффициентом кратности 0.
SUBSTRING(string_exp FROM start FOR length)	Возвращает символьную строку, извлекаемую из string_exp, начинающуюся с символа, позиция которого определяется аргументом start, а длина строки составляет столько символов, сколько указано в аргументе length .
TRIM(BOTH | LEADING | TRAILINGtrim_characterFROM string_exp)	Возвращает строку string_exp укороченную на количество символов, указанное в аргументе trim_character, с начала, с конца или с обоих концов строки.
UPPER (string_exp)	Возвращает строку, идентичную string_exp, в которой все символы нижнего регистра изменены на символы верхнего регистра.

Числовые функции

Все числовые функции возвращают числовые значения.

Аргументы, обозначенные numeric_exp, float_exp или integer_exp могут быть именем столбца, результатом другой скалярной функции или числовой константой, где исходные данные могут быть представлены числовым типом.

Функция	Описание
ABS (numeric_exp)	Возвращает абсолютное значение numeric_exp.
ACOS (float_exp)	Возвращает угол в радианах, равный арккосинусу float_exp.
ASIN (float_exp)	Возвращает угол в радианах, равный арксинусу float_exp.
ATAN (float_exp)	Возвращает угол в радианах, равный арктангенсу float_exp.
CEILING (numeric_exp)	Возвращает наименьшее целое значение, большее или равное numeric_exp.
COS (float_exp)	Возвращает косинус float_exp, где float_exp - угол в радианах.
FLOOR (numeric_exp)	Возвращает наибольшее целое значение, меньшее или равное numeric_exp.
LOG (float_exp)	Возвращает натуральный логарифм float_exp.
LOG10 (float_exp)	Возвращает логарифм по основанию 10 float_exp.
MOD (integer_exp1, integer_exp2)	Возвращает остаток от деления integer_exp1 на integer_exp2.
POWER (numeric_exp, integer_exp)	Возвращает значение numeric_exp в степени integer_exp.
ROUND (numeric_exp, integer_exp)	Возвращает значение numeric_exp, округленное до integer_exp знаков справа от десятичной запятой. Если integer_exp отрицательное, numeric_exp округляется до |integer_exp| знаков слева от десятичной запятой.
SIGN (numeric_exp)	Возвращает указатель знака numeric_exp. Если numeric_exp меньше нуля, возвращается –1. Если numeric_exp равно нулю, возвращается 0. Если numeric_exp больше нуля, возвращается 1.
SIN (float_exp)	Возвращает синус float_exp, где float_exp – угол в радианах.
TAN (float_exp)	Возвращает тангенс float_exp, где float_exp – угол в радианах.
TRUNCATE (numeric_exp, integer_exp)	Возвращает numeric_exp , усеченное до integer_exp знаков справа от десятичной запятой. Если integer_exp является отрицательным, numeric_exp сокращается до |integer_exp| знаков слева от десятичной запятой.

GROUP BY

Используется для сбора данных из нескольких записей и группировки результатов по одному или нескольким столбцам

SQL

SELECT STATE_NAME, SUM(POP1990) as TotalPopulation
FROM counties
GROUP BY STATE_NAME ORDER BY STATE_NAME

ArcObjects

pQueryDef.SubFields = "STATE_NAME, SUM(POP1990) as
TotalPopulation"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "GROUP BY STATE_NAME ORDER BY
STATE_NAME"

HAVING

Выражение where для GROUP BY.

SQL

SELECT department, MAX(salary) as Highest_salary
FROM employees
GROUP BY department HAVING MAX(salary) < 50000

ArcObjects

queryDef.SubFields = "department, MAX(salary) as Highest_salary"
queryDef.Tables = "employees"
pQueryDef.PostfixClause = "GROUP BY department HAVING MAX(salary)
< 50000"

JOINS

Объединяет записи из двух или более таблиц.

Cross Join (Перекрестное соединение)

SQL

SELECT Table1.name, Table1.Address, Table2.name, Table2.Salary
FROM Table1 CROSS JOIN Table2

ArcObjects

queryDef.SubFields = "Table1.name, Table1.Address, Table2.name,
Table2.Salary"
queryDef.Tables = "Table1 CROSS JOIN Table2"

Inner Join (Внутреннее соединение)

SQL

SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4 FROM Table1
INNER JOIN Table2 ON Table1.C1 = Table2.C3

ArcObjects

queryDef.SubFields = "Table1.C1, Table1.C2, Table2.C3,
Table2.C4"
queryDef.Tables = "Table1 INNER JOIN Table2 ON Table1.C1 =
Table2.C3"

Left Outer Join (Левое внешнее соединение)

SQL

SELECT Table1.C1, Table1.C2, Table2.C3, Table2.C4 FROM Table1
LEFT OUTER JOIN Table2 ON Table1.C1 = Table2.C3

ArcObjects

queryDef.SubFields = "Table1.C1, Table1.C2, Table2.C3,
Table2.C4"
queryDef.Tables = "Table1 LEFT OUTER JOIN Table2 ON Table1.C1 =
Table2.C3"

Right Outer Join (Правое внешнее соединение)

SQL

SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.C1 =
Table2.C3

ArcObjects

queryDef.SubFields = "*"
queryDef.Tables = "Table1 RIGHT OUTER JOIN Table2 ON Table1.C1 =
Table2.C3"

NULLIF

NULLIF возвращает значение NULL, если два его параметра равны; в противном случае возвращается значение первого параметра.

SQL

SELECT Location, NULLIF(Sales, Forecast) as Results FROM
StoreSales

ArcObjects

queryDef.SubFields = " Location, NULLIF(Sales, Forecast) as
Results"
queryDef.Tables = "StoreSales"

StoreSales:

Location	Sales	Forecast
Редландс	39000	55000
Palm Springs	60000	61000
Riverside	40000	40000

Результат:

Location	Results
Редландс	39000
Palm Springs	60000
Riverside	NULL

ORDER BY

Определяет порядок сортировки. Порядок может быть возрастающим (ASC) или убывающим (DESC) и может включать сравнение. Типы сравнения включают BINARY(BIN), CASESENSITIVE(CASE) и NOCASESENSITIVE(NOCASE). Двоичное сравнение учитывает регистр и диакритические знаки. Casesensitive различает буквы верхнего и нижнего регистров. Nocasesensitive не различает буквы верхнего и нижнего регистра.

SQL

SELECT STATE_NAME, POP1990
FROM counties ORDER BY STATE_NAME

ArcObjects

pQueryDef.SubFields = "STATE_NAME, POP1990"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY STATE_NAME"

SQL

SELECT STATE_NAME, POP1990
FROM counties ORDER BY STATE_NAME COLLATE BINARY ASC

ArcObjects

pQueryDef.SubFields = "STATE_NAME, POP1990"
pQueryDef.Tables = "counties"
pQueryDef.PostfixClause = "ORDER BY STATE_NAME COLLATE BINARY
ASC"