Skip to content

Instantly share code, notes, and snippets.

@emtwo

emtwo/gist:56b80a0f6b38ebdf32944a4970738d56

Created June 5, 2019 19:56
Show Gist options
  • Save emtwo/56b80a0f6b38ebdf32944a4970738d56 to your computer and use it in GitHub Desktop.
Save emtwo/56b80a0f6b38ebdf32944a4970738d56 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
-- Query generated by: sql/clients_daily_scalar_aggregates.sql.py
CREATE TEMP FUNCTION
udf_aggregate_map_sum(maps ANY TYPE) AS (STRUCT(ARRAY(
SELECT
AS STRUCT key,
SUM(value) AS value
FROM
UNNEST(maps),
UNNEST(key_value)
GROUP BY
key) AS key_value));
WITH
-- normalize client_id and rank by document_id
numbered_duplicates AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY
client_id,
submission_date_s3,
document_id
ORDER BY `timestamp`
ASC
) AS _n,
* REPLACE(LOWER(client_id) AS client_id)
FROM main_summary_v4
WHERE submission_date_s3 = @submission_date
AND client_id IS NOT NULL
),
-- Deduplicating on document_id is necessary to get valid SUM values.
deduplicated AS (
SELECT * EXCEPT (_n)
FROM numbered_duplicates
WHERE _n = 1
),
-- Aggregate by client_id using windows
windowed AS (
SELECT
ROW_NUMBER() OVER w1_unframed AS _n,
submission_date_s3 as submission_date,
client_id,
os,
app_version,
app_build_id,
channel,
ARRAY<STRUCT<
metric STRING,
agg_type STRING,
value STRUCT<key_value ARRAY<STRUCT<key INT64, value INT64>>>
>> [
('histogram_parent_webext_extension_startup_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_extension_startup_ms) OVER w1)),
('histogram_parent_memory_vsize', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_memory_vsize) OVER w1)),
('histogram_content_time_to_load_event_end_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_load_event_end_ms) OVER w1)),
('histogram_content_gc_max_pause_ms_2', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_gc_max_pause_ms_2) OVER w1)),
('histogram_parent_memory_heap_allocated', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_memory_heap_allocated) OVER w1)),
('histogram_parent_ssl_tls13_intolerance_reason_pre', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_ssl_tls13_intolerance_reason_pre) OVER w1)),
('histogram_parent_update_status_error_code_complete_stage', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_status_error_code_complete_stage) OVER w1)),
('histogram_parent_pwmgr_num_saved_passwords', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_num_saved_passwords) OVER w1)),
('histogram_content_fx_session_restore_startup_onload_initial_window_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_fx_session_restore_startup_onload_initial_window_ms) OVER w1)),
('histogram_parent_devtools_entry_point', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_devtools_entry_point) OVER w1)),
('histogram_content_memory_unique_content_startup', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_memory_unique_content_startup) OVER w1)),
('histogram_parent_time_to_first_key_input_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_time_to_first_key_input_ms) OVER w1)),
('histogram_parent_pwmgr_manage_opened', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_manage_opened) OVER w1)),
('histogram_parent_search_reset_result', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_search_reset_result) OVER w1)),
('histogram_content_composite_time', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_composite_time) OVER w1)),
('histogram_parent_content_frame_time', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_content_frame_time) OVER w1)),
('histogram_parent_webext_background_page_load_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_background_page_load_ms) OVER w1)),
('histogram_content_devtools_accessibility_time_active_seconds', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_devtools_accessibility_time_active_seconds) OVER w1)),
('histogram_parent_devtools_about_devtools_opened_key', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_devtools_about_devtools_opened_key) OVER w1)),
('histogram_content_gpu_process_launch_time_ms_2', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_gpu_process_launch_time_ms_2) OVER w1)),
('histogram_parent_fx_session_restore_restore_window_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_session_restore_restore_window_ms) OVER w1)),
('histogram_content_ipc_read_main_thread_latency_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_ipc_read_main_thread_latency_ms) OVER w1)),
('histogram_parent_cert_validation_success_by_ca', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_cert_validation_success_by_ca) OVER w1)),
('histogram_content_input_event_response_coalesced_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_input_event_response_coalesced_ms) OVER w1)),
('histogram_parent_fx_tab_switch_total_e10s_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_tab_switch_total_e10s_ms) OVER w1)),
('histogram_parent_pwmgr_blocklist_num_sites', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_blocklist_num_sites) OVER w1)),
('histogram_parent_update_status_error_code_complete_startup', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_status_error_code_complete_startup) OVER w1)),
('histogram_content_fx_urlbar_selected_result_method', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_fx_urlbar_selected_result_method) OVER w1)),
('histogram_parent_update_can_use_bits_notify', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_can_use_bits_notify) OVER w1)),
('histogram_content_time_to_first_scroll_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_first_scroll_ms) OVER w1)),
('histogram_parent_update_status_error_code_partial_stage', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_status_error_code_partial_stage) OVER w1)),
('histogram_parent_update_status_error_code_partial_startup', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_status_error_code_partial_startup) OVER w1)),
('histogram_content_memory_unique', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_memory_unique) OVER w1)),
('histogram_parent_places_autocomplete_6_first_results_time_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_places_autocomplete_6_first_results_time_ms) OVER w1)),
('histogram_content_search_service_init_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_search_service_init_ms) OVER w1)),
('histogram_parent_devtools_about_devtools_opened_reason', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_devtools_about_devtools_opened_reason) OVER w1)),
('histogram_parent_time_to_first_interaction_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_time_to_first_interaction_ms) OVER w1)),
('histogram_content_ssl_handshake_result', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_ssl_handshake_result) OVER w1)),
('histogram_content_http_pageload_is_ssl', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_http_pageload_is_ssl) OVER w1)),
('histogram_parent_pwmgr_form_autofill_result', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_form_autofill_result) OVER w1)),
('histogram_parent_pwmgr_manage_visibility_toggled', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_manage_visibility_toggled) OVER w1)),
('histogram_content_time_to_dom_content_loaded_start_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_dom_content_loaded_start_ms) OVER w1)),
('histogram_parent_update_state_code_partial_stage', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_state_code_partial_stage) OVER w1)),
('histogram_parent_update_download_code_partial', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_download_code_partial) OVER w1)),
('histogram_parent_fx_tab_close_time_anim_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_tab_close_time_anim_ms) OVER w1)),
('histogram_content_time_to_dom_complete_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_dom_complete_ms) OVER w1)),
('histogram_parent_a11y_consumers', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_a11y_consumers) OVER w1)),
('histogram_parent_update_state_code_complete_startup', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_state_code_complete_startup) OVER w1)),
('histogram_content_ghost_windows', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_ghost_windows) OVER w1)),
('histogram_parent_update_state_code_partial_startup', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_state_code_partial_startup) OVER w1)),
('histogram_parent_fx_tab_switch_update_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_tab_switch_update_ms) OVER w1)),
('histogram_parent_fx_urlbar_selected_result_type', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_urlbar_selected_result_type) OVER w1)),
('histogram_content_fx_page_load_ms_2', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_fx_page_load_ms_2) OVER w1)),
('histogram_parent_update_bits_result_partial', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_bits_result_partial) OVER w1)),
('histogram_parent_checkerboard_severity', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_checkerboard_severity) OVER w1)),
('histogram_content_memory_resident_fast', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_memory_resident_fast) OVER w1)),
('histogram_content_time_to_first_mouse_move_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_first_mouse_move_ms) OVER w1)),
('histogram_parent_webext_pageaction_popup_open_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_pageaction_popup_open_ms) OVER w1)),
('histogram_content_http_channel_disposition', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_http_channel_disposition) OVER w1)),
('histogram_parent_network_cache_v2_miss_time_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_network_cache_v2_miss_time_ms) OVER w1)),
('histogram_parent_webvr_time_spent_viewing_in_2d', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webvr_time_spent_viewing_in_2d) OVER w1)),
('histogram_content_devtools_toolbox_time_active_seconds', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_devtools_toolbox_time_active_seconds) OVER w1)),
('histogram_content_devtools_accessibility_service_time_active_seconds', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_devtools_accessibility_service_time_active_seconds) OVER w1)),
('histogram_parent_pwmgr_prompt_remember_action', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_prompt_remember_action) OVER w1)),
('histogram_content_plugin_shutdown_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_plugin_shutdown_ms) OVER w1)),
('histogram_parent_fx_urlbar_selected_result_index_by_type', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_urlbar_selected_result_index_by_type) OVER w1)),
('histogram_parent_webvr_time_spent_viewing_in_oculus', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webvr_time_spent_viewing_in_oculus) OVER w1)),
('histogram_content_tracking_protection_enabled', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_tracking_protection_enabled) OVER w1)),
('histogram_parent_time_to_non_blank_paint_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_time_to_non_blank_paint_ms) OVER w1)),
('histogram_parent_ssl_tls12_intolerance_reason_pre', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_ssl_tls12_intolerance_reason_pre) OVER w1)),
('histogram_content_network_cache_v2_hit_time_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_network_cache_v2_hit_time_ms) OVER w1)),
('histogram_parent_http_transaction_is_ssl', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_http_transaction_is_ssl) OVER w1)),
('histogram_parent_sandbox_rejected_syscalls', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_sandbox_rejected_syscalls) OVER w1)),
('histogram_parent_fx_urlbar_selected_result_index', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_urlbar_selected_result_index) OVER w1)),
('histogram_parent_devtools_fonteditor_n_fonts_rendered', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_devtools_fonteditor_n_fonts_rendered) OVER w1)),
('histogram_content_time_to_load_event_start_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_load_event_start_ms) OVER w1)),
('histogram_content_webvr_users_view_in', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_webvr_users_view_in) OVER w1)),
('histogram_parent_pwmgr_login_page_safety', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_login_page_safety) OVER w1)),
('histogram_content_cycle_collector_max_pause', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_cycle_collector_max_pause) OVER w1)),
('histogram_content_uptake_remote_content_result_1', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_uptake_remote_content_result_1) OVER w1)),
('histogram_parent_pwmgr_login_last_used_days', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_login_last_used_days) OVER w1)),
('histogram_parent_time_to_response_start_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_time_to_response_start_ms) OVER w1)),
('histogram_parent_pwmgr_saving_enabled', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_saving_enabled) OVER w1)),
('histogram_content_devtools_accessibility_picker_time_active_seconds', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_devtools_accessibility_picker_time_active_seconds) OVER w1)),
('histogram_parent_a11y_instantiated_flag', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_a11y_instantiated_flag) OVER w1)),
('histogram_parent_update_bits_result_complete', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_bits_result_complete) OVER w1)),
('histogram_parent_webext_browseraction_popup_open_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_browseraction_popup_open_ms) OVER w1)),
('histogram_content_ssl_handshake_version', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_ssl_handshake_version) OVER w1)),
('histogram_parent_update_state_code_complete_stage', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_state_code_complete_stage) OVER w1)),
('histogram_parent_memory_total', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_memory_total) OVER w1)),
('histogram_parent_devtools_fonteditor_font_type_displayed', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_devtools_fonteditor_font_type_displayed) OVER w1)),
('histogram_parent_gc_animation_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_gc_animation_ms) OVER w1)),
('histogram_parent_webext_storage_local_get_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_storage_local_get_ms) OVER w1)),
('histogram_parent_dns_lookup_time', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_dns_lookup_time) OVER w1)),
('histogram_content_time_to_dom_content_loaded_end_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_dom_content_loaded_end_ms) OVER w1)),
('histogram_parent_dns_failed_lookup_time', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_dns_failed_lookup_time) OVER w1)),
('histogram_parent_touch_enabled_device', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_touch_enabled_device) OVER w1)),
('histogram_parent_fx_new_window_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_new_window_ms) OVER w1)),
('histogram_parent_webext_storage_local_set_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_storage_local_set_ms) OVER w1)),
('histogram_parent_webext_browseraction_popup_preload_result_count', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_browseraction_popup_preload_result_count) OVER w1)),
('histogram_parent_time_to_first_click_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_time_to_first_click_ms) OVER w1)),
('histogram_parent_pwmgr_prompt_update_action', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_prompt_update_action) OVER w1)),
('histogram_content_memory_distribution_among_content', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_memory_distribution_among_content) OVER w1)),
('histogram_content_time_to_dom_interactive_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_dom_interactive_ms) OVER w1)),
('histogram_content_fx_searchbar_selected_result_method', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_fx_searchbar_selected_result_method) OVER w1)),
('histogram_content_memory_vsize_max_contiguous', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_memory_vsize_max_contiguous) OVER w1)),
('histogram_content_time_to_dom_loading_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_time_to_dom_loading_ms) OVER w1)),
('histogram_parent_webvr_time_spent_viewing_in_openvr', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webvr_time_spent_viewing_in_openvr) OVER w1)),
('histogram_content_gc_max_pause_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_gc_max_pause_ms) OVER w1)),
('histogram_parent_devtools_fonteditor_n_font_axes', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_devtools_fonteditor_n_font_axes) OVER w1)),
('histogram_parent_webext_content_script_injection_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_webext_content_script_injection_ms) OVER w1)),
('histogram_parent_pwmgr_num_passwords_per_hostname', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_pwmgr_num_passwords_per_hostname) OVER w1)),
('histogram_parent_content_paint_time', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_content_paint_time) OVER w1)),
('histogram_parent_fx_session_restore_startup_init_session_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_fx_session_restore_startup_init_session_ms) OVER w1)),
('histogram_content_geolocation_request_granted', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_geolocation_request_granted) OVER w1)),
('histogram_parent_update_download_code_complete', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_update_download_code_complete) OVER w1)),
('histogram_parent_network_cache_metadata_first_read_time_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_parent_network_cache_metadata_first_read_time_ms) OVER w1)),
('histogram_content_gpu_process_initialization_time_ms', 'summed-histogram', udf_aggregate_map_sum(ARRAY_AGG(histogram_content_gpu_process_initialization_time_ms) OVER w1))
] AS histogram_aggregates
FROM deduplicated
WINDOW
-- Aggregations require a framed window
w1 AS (
PARTITION BY
client_id,
submission_date_s3
ORDER BY `timestamp` ASC ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
),
-- ROW_NUMBER does not work on a framed window
w1_unframed AS (
PARTITION BY
client_id,
submission_date_s3
ORDER BY `timestamp` ASC
)
)
SELECT
* EXCEPT(_n)
FROM
windowed
WHERE
_n = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment