BigQuery의 모든것

9. user_id(int) event(string) event_date(string) 1 login_facebook 2019-05-14 1 write_posting 2019-05-14 1 write_comment 2019-05-14 1 view_posting 2019-05-14 1 view_posintg 2019-05-14 2 login_facebook 2019-05-14 2 view_posting 2019-05-14 2 view_posting 2019-05-14 2 write_comment 2019-05-14 2 logout 2019-05-14 2 login_facebook 2019-05-15 3 login_google 2019-05-15 3 write_posting 2019-05-15 3 view_posting 2019-05-15 3 purchase_item 2019-05-18 3 write_comment 2019-05-17 1 view_posting 2019-05-17 4 view_posintg 2019-05-17 5 purchase_item 2019-05-16

10. user_id event event_date 1 login_facebook 2019-05-14 1 write_posting 2019-05-14 1 write_comment 2019-05-14 1 view_posting 2019-05-14 1 view_posintg 2019-05-14

11. user_id event event_date 1 login_facebook 2019-05-14 1 write_posting 2019-05-14 1 write_comment 2019-05-14 1 view_posting 2019-05-14 1 view_posintg 2019-05-14

12. user_id event event_date unique total 1 login_facebook 2019-05-14 1 1 1 write_posting 2019-05-14 1 1 1 write_comment 2019-05-14 1 1 1 view_posting 2019-05-14 1 2

13. user_id event event_date unique total 1 login_facebook 2019-05-14 1 1 1 write_posting 2019-05-14 1 1 1 write_comment 2019-05-14 1 1 1 view_posting 2019-05-14 1 2

16. SELECT EXTRACT(DAY FROM DATE '2019-12-25') as the_day; +---------+ | the_day | +---------+ | 25 | +---------+

17. SELECT DATE_ADD(DATE “2019-03-25", INTERVAL 5 DAY) as five_days_later; +--------------------+ | five_days_later | +--------------------+ | 2019-03-30 | +--------------------+ SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff; +-----------+ | days_diff | +-----------+ | 559 | +-----------+

18. SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month; +------------+ | month | +------------+ | 2008-12-01 | +------------+

19. SELECT FORMAT_DATE("%x", DATE "2019-12-25") as US_format; +------------+ | US_format | +------------+ | 12/25/19 | +------------+ SELECT PARSE_DATE("%x", "12/25/19") as parsed; +------------+ | parsed | +------------+ | 2019-12-25 | +------------+

20. SELECT EXTRACT(HOUR FROM CAST(‘2019-12-25 14:00:00’ AS DATETIME) as hour; +---------+ | hour | +---------+ | 14 | +---------+

26. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

27. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

28. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

29. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

30. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#arrays-and-aggregation

31. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#arrays-and-aggregation

32. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#arrays-and-aggregation

33. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

34. https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

35. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct-type

36. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct-type

37. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct-type

38. https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

39. https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

40. https://cloud.google.com/bigquery/docs/views-intro

41. https://cloud.google.com/bigquery/docs/views-intro

42. https://cloud.google.com/bigquery/docs/views-intro

43. https://cloud.google.com/bigquery/docs/views-intro

44. https://cloud.google.com/bigquery/docs/views-intro

45. https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

46. https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

47. user_id visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4

48. user_id visit_month next_visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4

49. user_id visit_month next_visit_month 1004 1 3 1004 3 7 1004 7 8 1004 8 null 2112 3 6 2112 6 7 2112 7 null 3912 4 null

50. user_id visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4 user_id visit_month 1004 1 1004 3 1004 7 1004 8 user_id visit_month 2112 3 2112 6 2112 7 user_id visit_month 3912 4

51. user_id visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4 user_id visit_month 1004 1 1004 3 1004 7 1004 8 user_id visit_month 2112 3 2112 6 2112 7 user_id visit_month 3912 4

52. user_id visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4 user_id visit_month 1004 1 1004 3 1004 7 1004 8 user_id visit_month 2112 3 2112 6 2112 7 user_id visit_month next_visit_month 1004 1 3 1004 3 7 1004 7 8 1004 8 null 2112 3 6 2112 6 7 2112 7 null 3912 4 null user_id visit_month 3912 4

53. user_id visit_month next_visit_month next_two_ visit_month 1004 1 3 7 1004 3 7 8 1004 7 8 null 1004 8 null null 2112 3 6 7 2112 6 7 null 2112 7 null null 3912 4 null null

54. user_id visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4

55. user_id visit_month 1004 1 1004 3 1004 7 1004 8 2112 3 2112 6 2112 7 3912 4

56. user_id visit_month 1004 1 null 1004 3 1 1004 7 3 1004 8 7 2112 3 null 2112 6 3 2112 7 6 3912 4 null

57. https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

58. datetime demand 2019-05-15 14:00:00 13 15 2019-05-15 15:00:00 16 16 2019-05-15 16:00:00 20 20 2019-05-15 17:00:00 25 29 2019-05-15 18:00:00 41 32 2019-05-15 19:00:00 31 34 2019-05-15 20:00:00 29 30

59. https://blog.statsbot.co/sql-window-functions-tutorial-b5075b87d129

60. https://blog.statsbot.co/sql-window-functions-tutorial-b5075b87d129

61. https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

62. https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

63. https://cloud.google.com/bigquery/docs/reference/standard-sql/user-deﬁned-functions

64. https://cloud.google.com/bigquery/docs/reference/standard-sql/user-deﬁned-functions#sql-udf-structure

65. https://cloud.google.com/bigquery/docs/reference/standard-sql/user-deﬁned-functions#supported-external-udf-languages

66. https://cloud.google.com/bigquery/docs/reference/standard-sql/user-deﬁned-functions#including-external-libraries

67. https://cloud.google.com/bigquery/docs/partitioned-tables

68. https://cloud.google.com/bigquery/docs/querying-wildcard-tables

69. https://cloud.google.com/bigquery/docs/creating-column-partitions

70. https://cloud.google.com/bigquery/docs/querying-partitioned-tables

71. https://cloud.google.com/bigquery/docs/querying-partitioned-tables

73. https://cloud.google.com/bigquery/docs/scheduling-queries

74. https://cloud.google.com/bigquery/docs/scheduling-queries

75. https://cloud.google.com/bigquery/docs/scheduling-queries

76. https://cloud.google.com/bigquery/docs/scheduling-queries

77. https://cloud.google.com/bigquery/docs/scheduling-queries

78. https://cloud.google.com/bigquery/docs/scheduling-queries

79. https://cloud.google.com/bigquery/docs/scheduling-queries

80. https://cloud.google.com/bigquery/docs/scheduling-queries

81. https://cloud.google.com/bigquery/docs/scheduling-queries

85. https://support.google.com/ﬁrebase/answer/7029846

86. https://support.google.com/ﬁrebase/answer/7029846

87. https://support.google.com/ﬁrebase/answer/6317485

88. https://support.google.com/ﬁrebase/answer/6317485

89. https://medium.com/ﬁrebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in- analytics-fb828f890b42

90. https://medium.com/ﬁrebase-developers/using-the-unnest-function-in-bigquery-to-analyze-event-parameters-in- analytics-fb828f890b42

92. https://www.slideshare.net/lynnlangit/google-cloud-and-data-pipeline-patterns

93. https://medium.com/teads-engineering/give-meaning-to-100-billion-analytics-events-a-day-d6ba09aa8f44

94. https://wecode.wepay.com/posts/bigquery-wepay

97. https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#sql-syntax