發布商查詢語言 (PQL) 開發人員指南

PQL 語法和用法

PQL 是類似 SQL 的語言,可用來查詢物件。PQL 語法與 SQL 類似,但有幾項差異。本節將說明 PQL 語法,以及如何使用 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 - 表示零或多個條件的組合,可選擇使用 AND 或 OR 詞組進行彙整。您可以用括號括住 AND 或 OR 詞組。執行查詢 "" (空白字串) 會傳回所有項目。

    範例: WHERE width = 728
    WHERE width = 728 AND height = 90
    WHERE (width = 728 AND height = 90) OR id IN (5008, 8745, 3487)

  • OR - 彙整多個條件,只有其中一個條件必須為 true。如要檢查單一屬性的多個值,建議您使用 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 查詢。舉例來說,您可以依據包含 idnamewidthheight 篩選廣告素材屬性。
  • <value> - 字串值應以單引號 (') 括住。數字值可以是引號或不加引號。不支援萬用字元。
  • IN - 比較清單中每個項目的值和清單中每個項目;如有相符結果,就是正面比對。IN 運算子等同於多個 = 查詢,每個值代表一個查詢,一起進行 OR 邏輯運算。這些值會以逗號分隔值清單指定,並用括號括住:(a, b, c)。系統會評估清單中的所有值。

    範例: WHERE name IN ('CompanyNameA', 'CompanyNameB')

  • NOT IN - 比較清單中每個項目的值;如果沒有相符,則為正比對。NOT IN 運算子等同於多個 != 查詢,每個值代表一個查詢,一起進行 OR 邏輯運算。這些值會以逗號分隔值清單指定,並用括號括住:(a, b, c)。系統會評估清單中的所有值。

    範例: WHERE NOT name IN ('CompanyNameA', 'CompanyNameB')

  • LIKE:可讓您使用萬用字元字串比對功能查詢物件。百分比符號 (%) 代表零、一個或多個字元。使用配對將要相符的搜尋字串納入。

    範例: WHERE name LIKE 'foo %searchString% bar'
    WHERE name LIKE 'Aus%'

  • IS NULL:可讓您查詢屬性值未定義的物件。常見的例子是查詢父項 ID 為空值的 AdUnit,藉此查詢根 AdUnit

    範例:WHERE parentId IS NULL

  • <bind variable> - 您可以在 PQL 查詢中使用 Value 物件來取代硬式編碼的 <value> 值。在 PQL 中,繫結變數是以 : (冒號開頭,開頭不含空格) 的字串名稱所參照。

    範例 (建立查詢並輸入兩個變數,取代硬式編碼的 idstatus 屬性值):

    // 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

Python

設定報表查詢

首先建立報表工作,並指定維度、資料欄和日期範圍等報表參數。

# 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='v202402')

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 資料表下載資料

如要將報表與其他委刊項資料進行比對,您可以使用 Line_Item PQL 資料表。

# 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 查看