Составление запросов на выборку данных для модели машинного обучения

Общие сведения

Для выборки тренировочных данных или данных для прогнозирования сервиса машинного обучения используется экземпляр класса Terrasoft.Core.DB.Select (см. "Сервис машинного обучения" и "Как реализовать пользовательскую предиктивную модель"). Он динамически интерпретируется с помощью Terrasoft.Configuration.ML.QueryInterpreter.

При составлении выражения запроса в качестве аргумента типа Terrasoft.Core.UserConnection в конструкторе Select следует использовать предоставляемую переменную userConnection. Также обязательным в выражении запроса является наличие колонки с псевдонимом “Id” — уникальным идентификатором экземпляра целевого объекта.

Так как выражение Select может быть довольно сложным, то для упрощения его читаемости были добавлены следующие возможности:

• Динамическое добавление типов для интерпретатора.
• Использование локальных переменных.
• Использование утилитного класса Terrasoft.Configuration.QueryExtensions.

Динамическое добавление типов для интерпретатора

Существует возможность динамически добавлять типы для интерпретатора. Для этого класс QueryInterpreter предоставляет методы RegsiterConfigurationType и RegisterType. Их можно напрямую использовать в выражении. Например, вместо непосредственного использования идентификатора типа:

new Select(userConnection)
        .Column("Id")
        .Column("Body")
    .From("Activity")
    .Where("TypeId").IsEqual(Column.Const("E2831DEC-CFC0-DF11-B00F-001D60E938C6"));

можно использовать название константы из динамически зарегистрированного перечисления:

RegisterConfigurationType("ActivityConsts");
new Select(userConnection)
        .Column("Id")
        .Column("Body")
    .From("Activity")
    .Where("TypeId").IsEqual(Column.Const(ActivityConsts.EmailTypeUId));

Использование локальных переменных

Существует возможность использовать локальные переменные для предотвращения дублирования кода и более удобного структурирования. Ограничение: тип переменной должен быть статически вычисляем и определяется ключевым словом var.

Например, запрос с повторяющимся использованием делегатов:

new Select(userConnection)
        .Column("Id")
        .Column("Body")
    .From("Activity")
    .Where("CreatedOn").IsGreater(Func.DateAddMonth(-1, Func.CurrentDateTime()))
    .And("StartDate").IsGreater(Func.DateAddMonth(-1, Func.CurrentDateTime()));

можно переписать следующим образом:

var monthAgo = Func.DateAddMonth(-1, Func.CurrentDateTime());
 
new Select(userConnection)
        .Column("Id")
        .Column("Body")
    .From("Activity")
    .Where("StartDate").IsGreater(monthAgo)
    .And("ModifiedOn").IsGreater(monthAgo);

Использование утилитного класса Terrasoft.Configuration.QueryExtensions

Утилитный класс Terrasoft.Configuration.QueryExtensions предоставляет несколько расширяющих методов для Terrasoft.Core.DB.Select. Это позволяет составлять более компактные запросы.

Для всех расширяющих методов в качестве аргумента object sourceColumn могут быть использованы следующие типы (они будут трансформированы в Terrasoft.Core.DB.QueryColumnExpression):

• System.String — название колонки в формате "TableAlias.ColumnName as ColumnAlias" (где TableAlias и ColumnAlias опциональны) или “*” — все колонки.
• Terrasoft.Core.DB.QueryColumnExpression — будет добавлен без изменений.
• Terrasoft.Core.DB.IQueryColumnExpressionConvertible — будет сконвертировано.
• Terrasoft.Core.DB.Select — будет рассмотрен как подзапрос.

Примеры использования Terrasoft.Configuration.QueryExtensions

1. Метод public static Select Cols(this Select select, params object[] sourceColumns)

Добавляет в запрос указанные колонки или подвыражения.

Используя расширяющий метод Cols(), вместо громоздкого выражения:

new Select(userConnection)
        .Column("L", "Id")
        .Column("L", "QualifyStatusId")
        .Column("L", "LeadTypeId")
        .Column("L", "LeadSourceId")        
        .Column("L", "LeadMediumId").As("LeadChannel")
        .Column("L", "BusinesPhone").As("KnownBusinessPhone")    
    .From("Lead").As("L");

можно записать:

new Select(userConnection).Cols(
        "L.Id",
        "L.QualifyStatusId",
        "L.LeadTypeId",
        "L.LeadSourceId",
        "L.LeadMediumId AS LeadChannel",
        "L.BusinesPhone AS KnownBusinessPhone")
    .From("Lead").As("L");

2. Метод public static Select Count(this Select select, object sourceColumn)

Добавляет в запрос агрегирующую колонку для вычисления количества непустых значений.

Например, вместо:

var activitiesCount = new Select(userConnection)
    .Column(Func.Count(Column.Asterisk()))
    .From("Activity")

можно записать:

var activitiesCount = new Select(userConnection)
    .Count("*") // Здесь можно указать и название колонки.
    .From("Activity")

3. Метод public static Select Coalesce(this Select select, params object[] sourceColumns)

Добавляет в запрос колонку с функцией определения первого значения, не равного NULL.

Например, вместо:

new Select(userConnection)
    .Cols("L.Id")
    .Column(Func.Coalesce(
            Column.SourceColumn("L", "CountryId"),
            Column.SourceColumn("L", "CountryId"),
            Column.SourceColumn("L", "CountryId")))
        .As("CountryId")
    .From("Lead").As("L")
        .LeftOuterJoin("Contact").As("C").On("L", "QualifiedContactId").IsEqual("C", "Id")
         .LeftOuterJoin("Account").As("A").On("L", "QualifiedAccountId").IsEqual("A", "Id");

можно записать:

new Select(userConnection)
        .Cols("L.Id")
        .Coalesce("L.CountryId", "C.CountryId", "A.CountryId").As("CountryId")
    .From("Lead").As("L")
        .LeftOuterJoin("Contact").As("C").On("L", "QualifiedContactId").IsEqual("C", "Id")
        .LeftOuterJoin("Account").As("A").On("L", "QualifiedAccountId").IsEqual("A", "Id");

4. Метод public static Select DateDiff(this Select select, DateDiffQueryFunctionInterval interval, object startDateExpression, object endDateExpression)

Добавляет в запрос колонку с определением разницы дат.

Например, вместо:

new Select(_userConnection)
        .Cols("Id")
        .Column(Func.DateDiff(DateDiffQueryFunctionInterval.Day,
            Column.SourceColumn("L", "CreatedOn"), Func.CurrentDateTime())).As("LeadAge")
    .From("Lead").As("L");

можно записать:

var day = DateDiffQueryFunctionInterval.Day;
new Select(userConnection)
        .Cols("L.Id")
        .DateDiff(day, "L.CreatedOn", Func.CurrentDateTime()).As("LeadAge")
    .From("Lead").As("L");

5. public static Select IsNull(this Select select, object checkExpression, object replacementValue)

Добавляет в запрос колонку с функцией замены значения NULL замещающим выражением.

Например, вместо:

new Select(userConnection).Cols("Id")
        .Column(Func.IsNull(
               Column.SourceColumn("L", "CreatedOn"), 
               Column.SourceColumn("L", "ModifiedOn")))
    .From("Lead").As("L");

можно записать:

new Select(userConnection).Cols("L.Id")
        .IsNull("L.CreatedOn", "L.ModifiedOn")
    .From("Lead").As("L");