Руководство разработчика по языку запросов издателя (PQL)

Синтаксис и использование PQL

PQL — это SQL-подобный язык для запросов к объектам. Синтаксис PQL аналогичен синтаксису SQL, с некоторыми отличиями, описанными здесь. В этом разделе описывается синтаксис PQL и способы его использования для фильтрации различных типов объектов.

Синтаксис PQL можно резюмировать следующим образом:

[WHERE <condition> {[AND | OR] <condition> ...}]
[ORDER BY <property> [ASC | DESC]]
[LIMIT {[<offset>,] <count>} | {<count> OFFSET <offset>}]

<condition> := <property> { = | != } <value>
<condition> := <property> { = | != } <bind variable>
<condition> := <property> IN <list>
<condition> := NOT <property> IN <list>
<condition> := <property> LIKE <wildcard%match>
<condition> := <property> IS NULL
<bind variable> := :<name>

Примечания

  • Ключевые слова PQL не чувствительны к регистру.
  • Строки автоматически экранируются при использовании в параметрах привязки. В противном случае:

    • Для строки, заключенной в одинарные кавычки (апострофы), избегайте любого дополнительного апострофа, записывая его в виде пары одинарных кавычек.

      Пример: "WHERE name = 'Company''s name'"

Ключевые слова (без учета регистра)

  • WHERE — выражает набор из нуля или более условий, опционально объединенных с помощью фраз «И» или «ИЛИ». Вы можете объединить фразы И или ИЛИ круглыми скобками. Выполнение запроса "" (пустая строка) возвращает все.

    Примеры: WHERE width = 728
    WHERE width = 728 AND height = 90
    WHERE (width = 728 AND height = 90) OR id IN (5008, 8745, 3487)

  • OR — объединяет несколько условий, только одно из которых должно быть истинным. Если вы хотите проверить любое из нескольких значений для одного свойства, рассмотрите возможность использования предложения IN .

    Пример: WHERE width = 728 OR height = 90

  • AND — объединяет несколько условий, которые все должны быть удовлетворены, с помощью предложения AND.

    Пример: WHERE type = 'AGENCY' AND name IN ('CompanyNameA', 'CompanyNameB')

  • ORDER BY — сортирует возвращаемые результаты либо по возрастанию ( ASC , где «A» — первая), либо по убыванию ( DESC , где «A» — последняя). Если направление не указано, по умолчанию используется ASC . Если это предложение не включено, по умолчанию в первом поле используется ASC .

    Пример: WHERE id IN (5008, 8745, 3487) ORDER BY id

  • LIMIT — количество возвращаемых результатов. LIMIT также может включать <offset> , который указывает, на сколько строк от начала можно сместить ваш набор результатов.

    Примеры (оба примера возвращают один и тот же набор результатов):
    WHERE type = 'AGENCY' LIMIT 50 OFFSET 50
    WHERE type = 'AGENCY' LIMIT 50,50

  • OFFSET — смещение в наборе результатов, с которого начинается возврат значений. Используйте это для просмотра результатов.

    Пример (возвращает результаты 51–100):
    WHERE type = 'AGENCY' LIMIT 50 OFFSET 50 .

  • <property> — одно из свойств, предоставляемых объектом. Каждый объект предоставляет различные свойства, по которым можно фильтровать данные с помощью PQL; Обычно вы не можете фильтровать все свойства, поддерживаемые объектом, поэтому проверьте список ниже, чтобы узнать, какие свойства поддерживают запросы PQL. Например, свойства креатива, по которым можно фильтровать, включают id , name , width и height .
  • <value> — строковые значения должны быть заключены в одинарные кавычки ('). Числовые значения могут быть заключены в кавычки или не заключены в кавычки. Подстановочные знаки не поддерживаются.
  • IN — сравнивает значение свойства с каждым элементом списка; если какой-либо из них совпадает, то это положительное совпадение. Оператор IN эквивалентен множеству запросов = , по одному для каждого значения, которые объединяются ИЛИ. Значения указываются в виде списка значений, разделенных запятыми и заключенных в круглые скобки: (a, b, c). Оцениваются все значения в списке.

    Пример: WHERE name IN ('CompanyNameA', 'CompanyNameB')

  • NOT IN — сравнивает значение свойства с каждым элементом списка; если ни одно совпадение не соответствует, это положительное совпадение. Оператор NOT IN эквивалентен множеству запросов != , по одному для каждого значения, которые объединяются ИЛИ. Значения указываются в виде списка значений, разделенных запятыми и заключенных в круглые скобки: (a, b, c). Оцениваются все значения в списке.

    Пример: WHERE NOT name IN ('CompanyNameA', 'CompanyNameB')

  • LIKE — позволяет запрашивать объекты, используя сопоставление строк с подстановочными знаками. Знак процента ( % ) представляет ноль, один или несколько символов. Используйте пару, чтобы заключить в нее искомую строку поиска.

    Примеры: WHERE name LIKE 'foo %searchString% bar'
    WHERE name LIKE 'Aus%'

  • IS NULL — позволяет запрашивать объекты с неопределенным значением свойства. Классическим примером этого является запрос корневого AdUnit путем запроса AdUnit с нулевым родительским идентификатором.

    Пример: WHERE parentId IS NULL .

  • <bind variable> — вы можете использовать объекты Value вместо жестко запрограммированных значений <value> в вашем запросе PQL. Переменная связывания в PQL упоминается с использованием строкового имени без пробелов, начинающегося с : (двоеточия).

    Пример (создается запрос и вводятся две переменные вместо жестко запрограммированных значений свойств id и status ):

    // Create two mapped parameters: id and status
String_ValueMapEntry[] values = new String_ValueMapEntry[2];
values[0] = new String_ValueMapEntry("id", new NumberValue(null, "123"));
values[1] = new String_ValueMapEntry("status", new TextValue(null, "APPROVED"));

// Create our statement and map our bind variables
Statement statement = new Statement();
statement.setQuery("WHERE id = :id AND status = :status LIMIT 500");
statement.setValues(values);
  • Поля DateTime . Вы можете фильтровать по дате и времени, присвоив значение DateTime переменной привязки или используя строку, отформатированную в соответствии с ISO 8601.
    // Create a bind variable: startDateTime
String_ValueMapEntry[] values = new String_ValueMapEntry[1];
values[0] = new String_ValueMapEntry("startDateTime", new DateTimeValue(null, dateTime));

// Create our statement and map our bind variables
Statement statement = new Statement();
statement.setQuery("WHERE endDateTime < '2019-01-01T00:00:00' AND startDateTime > :startDateTime LIMIT 500");
statement.setValues(values);

Получение таблиц соответствий с помощью PQL

Таблицы соответствий предоставляют механизм поиска необработанных значений, содержащихся в файлах передачи данных, что позволяет сопоставлять информацию о показе рекламы (например, рекламный блок или позицию) с заранее назначенными значениями, хранящимися в базе данных.

Если вы запускаете отчеты через ReportService или с помощью отчетов о передаче данных , возможно, вам захочется дополнить данные отчета дополнительными полями. Например, с помощью отчета с измерением LINE_ITEM_ID или события передачи данных с полем LineItemId вы можете создать таблицу соответствия, включающую дату начала, дату окончания, тип, статус и другие полезные атрибуты каждой позиции.

Существует несколько способов реализации этой функции сопоставления:

  1. Используйте готовые таблицы соответствия, предоставленные службой передачи данных BigQuery . Обратите внимание, что эти таблицы соответствия не содержат все поля сущности.
  2. Эффективный подход — использовать любую из доступных таблиц PublisherQueryLanguageService .
  3. Если для сущности нет таблицы BigQuery или PQL или в таблице отсутствуют нужные вам поля, вы можете напрямую воспользоваться службой этой сущности, например OrderService .

Питон

Настройка запроса отчета

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

# Set the start and end dates of the report to run (past 8 days).
end_date = date.today()
start_date = end_date - timedelta(days=8)

# Create report job.
report_job = {
    'reportQuery': {
        'dimensions': ['LINE_ITEM_ID', 'LINE_ITEM_NAME'],
        'columns': ['AD_SERVER_IMPRESSIONS', 'AD_SERVER_CLICKS',
                    'AD_SERVER_CTR', 'AD_SERVER_CPM_AND_CPC_REVENUE',
                    'AD_SERVER_WITHOUT_CPD_AVERAGE_ECPM'],
        'dateRangeType': 'CUSTOM_DATE',
        'startDate': start_date,
        'endDate': end_date
    }
}

Скачать отчет 

# Initialize a DataDownloader.
report_downloader = client.GetDataDownloader(version='v202408')

try:
  # Run the report and wait for it to finish.
  report_job_id = report_downloader.WaitForReport(report_job)
except errors.AdManagerReportError as e:
  print('Failed to generate report. Error was: %s' % e)

with tempfile.NamedTemporaryFile(
    suffix='.csv.gz', mode='wb', delete=False) as report_file:
  # Download report data.
  report_downloader.DownloadReportToFile(
      report_job_id, 'CSV_DUMP', report_file)

Загрузите данные из таблицы Line_Item PQL.

Чтобы сопоставить отчет с дополнительными данными о позиции, вы можете использовать таблицу PQL Line_Item . 

# Create a PQL query to fetch the line item data
line_items_pql_query = ('SELECT Id, LineItemType, Status FROM LineItem')

# Download the response from PQL select statement
line_items = report_downloader.DownloadPqlResultToList(line_items_pql_query)

Объединение данных отчета с данными позиции

В этом примере используется библиотека pandas , поскольку она значительно упрощает работу с табличными данными. Здесь он используется для объединения данных отчета с данными PQL для создания таблицы соответствия.

# Use pandas to join the two csv files into a match table
report = pandas.read_csv(report_file.name)
line_items = pandas.DataFrame(data=line_items[1:], columns=line_items[0])
merged_result = pandas.merge(report, line_items,
                             left_on='Dimension.LINE_ITEM_ID', right_on='id')
merged_result.to_csv('~/complete_line_items_report.csv', index=False)
Посмотреть на GitHub