bigquery-查找过去365天内每个值的最新条目

14ifxucb  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(359)

我有我的gmail日志被发送到bq，我试图找到最后一次电子邮件被发送到一个地址列表（只拉最新的信息）
以下是我目前使用的查询：

SELECT
  FORMAT_UTC_USEC(event_info.timestamp_usec) AS timestamp,
  message_info.subject,
  message_info.source.address,
  message_info.destination.address,
  message_info.rfc2822_message_id
FROM (TABLE_DATE_RANGE([domain-com-gmail-logs.gmail_logs_dataset.daily_],
      DATE_ADD(CURRENT_TIMESTAMP(), -365, 'DAY'),
      CURRENT_TIMESTAMP()))
WHERE
  message_info.destination.address IN ("email1@domain.com", "email2@domain.com", "email3@domain.com", "email4@domain.com", "email5@domain.com")
GROUP BY
  timestamp,
  message_info.subject,
  message_info.source.address,
  message_info.destination.address,
  message_info.rfc2822_message_id

但是，这将返回在过去365天内有电子邮件发送到该地址的每个示例。
我如何限制它，使它只提取where子句中每个匹配项的最新电子邮件数据？
此外，这是使用谷歌的'传统'sql-有什么想法，我可以转换成标准的？
编辑-更新的模式和清晰度

+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| Field Name                                                                        | Type    | Mode     | Policy tags | Description |
+===================================================================================+=========+==========+=============+=============+
| event_info                                                                        | RECORD  | REQUIRED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| event_info. timestamp_usec                                                        | INTEGER | REQUIRED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| event_info. elapsed_time_usec                                                     | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| event_info. success                                                               | BOOLEAN | REQUIRED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info                                                                      | RECORD  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. action_type                                                         | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. rfc2822_message_id                                                  | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. subject                                                             | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. payload_size                                                        | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. source                                                              | RECORD  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.source. address                                                      | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.source. service                                                      | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.source. selector                                                     | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.source. from_header_address                                          | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.source. from_header_displayname                                      | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. destination                                                         | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. address                                                 | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. service                                                 | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. selector                                                | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. smime_signature_verification_success                    | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. smime_decryption_success                                | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. smime_parsing_success                                   | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. smime_extraction_success                                | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.destination. rcpt_response                                           | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. flattened_destinations                                              | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. description                                                         | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. connection_info                                                     | RECORD  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. client_ip                                           | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_in_connect_ip                                  | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_out_connect_ip                                 | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. failed_smtp_out_connect_ip                          | STRING  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_tls_state                                      | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_reply_code                                     | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. tls_required_but_unavailable                        | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_out_remote_host                                | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_user_agent_ip                                  | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. is_intra_domain                                     | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. dmarc_pass                                          | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. dmarc_published_domain                              | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. client_host_zone                                    | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_response_reason                                | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. ip_geo_city                                         | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. ip_geo_country                                      | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. authenticated_domain                                | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info.authenticated_domain. name                           | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info.authenticated_domain. type                           | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. is_internal                                         | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. dkim_pass                                           | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. spf_pass                                            | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_tls_version                                    | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.connection_info. smtp_tls_cipher                                     | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. is_spam                                                             | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. is_policy_check_for_sender                                          | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. num_message_attachments                                             | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. message_set                                                         | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.message_set. type                                                    | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. smtp_relay_error                                                    | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. upload_error_category                                               | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. structured_policy_log_info                                          | RECORD  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info. exchange_journal_info                    | RECORD  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info.exchange_journal_info. timestamp          | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info.exchange_journal_info. unknown_recipients | STRING  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info.exchange_journal_info. recipients         | STRING  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info.exchange_journal_info. rfc822_message_id  | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info. detected_file_types                      | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info.detected_file_types. category             | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.structured_policy_log_info.detected_file_types. mime_type            | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. triggered_rule_info                                                 | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info. policy_holder_address                           | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info. consequence                                     | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.consequence. action                              | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.consequence. reason                              | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.consequence. subconsequence                      | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.consequence.subconsequence. action               | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.consequence.subconsequence. reason               | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info. string_match                                    | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.string_match. type                               | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.string_match. predefined_detector_name           | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.string_match. source                             | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.string_match. attachment_name                    | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.string_match. matched_string                     | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info.string_match. match_expression                   | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info. spam_label_modifier                             | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info. rule_type                                       | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.triggered_rule_info. rule_name                                       | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. flattened_triggered_rule_info                                       | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. smime_sign_message                                                  | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. smime_encrypt_message                                               | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. smime_packaging_success                                             | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. smime_extraction_success                                            | BOOLEAN | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. smime_content_type                                                  | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. link_domain                                                         | STRING  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. attachment                                                          | RECORD  | REPEATED |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.attachment. sha256                                                   | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.attachment. file_extension_type                                      | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.attachment. malware_family                                           | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info. spam_info                                                           | RECORD  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.spam_info. disposition                                               | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.spam_info. classification_reason                                     | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.spam_info. ip_whitelist_entry                                        | STRING  | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
| message_info.spam_info. classification_timestamp_usec                             | INTEGER | NULLABLE |             |             |
+-----------------------------------------------------------------------------------+---------+----------+-------------+-------------+
sqlgoogle-bigquerylegacy-sql

1条答案

按热度按时间
wljmcqd8

wljmcqd81#

我正在尝试查找上一次向地址列表发送电子邮件的时间
你好像在问这样的问题：

SELECT message_info.destination.address, MAX(event_info.timestamp_usec)
FROM `domain-com-gmail-logs.gmail_logs_dataset.daily_` d
WHERE message_info.destination.address IN ('email1@domain.com', 'email2@domain.com', 'email3@domain.com', 'email4@domain.com', 'email5@domain.com') AND
      _table_suffix >= DATE_ADD(CURRENT_TIMESTAMP(), -365, 'DAY')
GROUP BY message_info.destination.address;
赞(0）分享  回复(0）举报  2021-07-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com