현재 카프카로 데이터를 보내기전에 redis를 사용하여 발급된 쿠폰 개수에 대한 동시성 처리를 해서 개수에 대한 검증 로직이 있다고 앞서 강의에서 얘기를 하셨습니다. 그러면 발급된 쿠폰 개수가 100개 되고 난 이후의 요청은 그냥 무시하면 되나요? 쿠폰이 천개, 만개 이렇게 매우 많다면 쿠폰 발급에 대한 요청을 바로 DB에 저장을 하면 DB에 부하가 심해져서 카프카를 도입해 이러한 부하를 낮춘다고 이해를 했습니다. 궁금한 점은 DB에 대한 부하를 낮춰도 이벤트 시기에 수많은 사용자들의 요청으로 인해 서버 자체에 대한 부하는 굉장히 심할꺼 같은데 서버에 대한 부하를 낮추는 방법은 없나요? 현재 흐름이 쿠폰 요청 -> 서버 -> reids에서 쿠폰 개수 확인 -> 카프카 -> 컨슈머 -> DB 인데 이러한 흐름을 요청 -> 서버 -> 카프카 -> 컨슈머 -> redis에서 쿠폰 개수 확인 -> DB 이렇게 바꾸는 방식은 어떤지 궁금합니다. 이런식으로 하면 서버쪽에서 카프카로 데이터를 비동기로 전송한다면 서버 자체에도 부하가 낮아지지 않을까 라는 생각이 들어서 여쭤 봅니다. redis streams나 래빗엠큐 같은 다른 기능들도 있는데 Kafka를 사용하신 이유가 궁금합니다. 만약 쿠폰 발급이 100개처럼 적게 발급하는 시스템이라면 굳이 카프카를 도입을 할 필요가 없는건가요? publisher가 카프카로 데이터를 보내면 consumer가 바로 받아와서 DB에 처리를 하면 안되겠죠? 이렇게 처리를 하면 바로 DB에 저장을 하는 상황이니 DB에 부하가 심해진다고 생각합니다. 현재 강사님이 알려주신 코드를 바탕으로 시스템을 구축하고 여기에 부하 테스트를 한다고 했을때 어떤 식으로 단계를 잡아서 부하 테스트를 하면 좋을지 조언을 해주실 수 있을까요 한번에 너무 많은 질문해서 죄송합니다.
팬심 또는 언젠간을 위해 강의를 전부 구매했습니다. 구매 후 기본편 까지는 강의를 다봤거든요. 그런데 교안을 너무 잘만드셔서 교안만 봐도 될꺼같다 라는 생각이 들더라구요. 물론, 강의를 진득하게 듣고 이해하여, 그것을 체화하면 자바에 대해 더 깊게 이해할 수 있겠지만, 문제는 역시 트레이드오프... 시간이 너무 많이 걸리네요. 자바 고급 2편까지 1달 반에서 2달 내로 끝내고 싶은데 강의를 들으면 도저히 그 시간을 맞출 수 없을꺼같아서 질문해봅니다. 제목 그대로 교안만으로도 충분할까요?
자바와 스프링 부트로 생애 최초 서버 만들기, 누구나 쉽게 개발부터 배포까지! [서버 개발 올인원 패키지]
안녕하세요 강사님 영상으로 스프링을 처음 배우기 시작하여 공부 중인데 도서관 애플리케이션에서 사용자를 등록은 가능하나 삭제를 눌렀을때 서버 내부 오류입니다라는 내용이 나오는데요 에러 내용은 .. java.lang.IllegalArgumentException: Name for argument of type [java.lang.String] not specified, and parameter name information not available via reflection. Ensure that the compiler uses the '-parameters' flag. at org.springframework.web.method.annotation.AbstractNamedValueMethodArgumentResolver.updateNamedValueInfo( AbstractNamedValueMethodArgumentResolver.java:186 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.method.annotation.AbstractNamedValueMethodArgumentResolver.getNamedValueInfo( AbstractNamedValueMethodArgumentResolver.java:161 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.method.annotation.AbstractNamedValueMethodArgumentResolver.resolveArgument( AbstractNamedValueMethodArgumentResolver.java:107 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.method.support .HandlerMethodArgumentResolverComposite.resolveArgument( HandlerMethodArgumentResolverComposite.java:122 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.method.support .InvocableHandlerMethod.getMethodArgumentValues( InvocableHandlerMethod.java:224 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.method.support .InvocableHandlerMethod.invokeForRequest( InvocableHandlerMethod.java:178 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle( ServletInvocableHandlerMethod.java:118 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod( RequestMappingHandlerAdapter.java:926 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal( RequestMappingHandlerAdapter.java:831 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle( AbstractHandlerMethodAdapter.java:87 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.DispatcherServlet.doDispatch( DispatcherServlet.java:1089 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.DispatcherServlet.doService( DispatcherServlet.java:979 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.FrameworkServlet.processRequest( FrameworkServlet.java:1014 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at org.springframework.web.servlet.FrameworkServlet.doDelete( FrameworkServlet.java:936 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at jakarta.servlet.http.HttpServlet.service( HttpServlet.java:596 ) ~[tomcat-embed-core-10.1.31.jar:6.0] at org.springframework.web.servlet.FrameworkServlet.service( FrameworkServlet.java:885 ) ~[spring-webmvc-6.1.14.jar:6.1.14] at jakarta.servlet.http.HttpServlet.service( HttpServlet.java:658 ) ~[tomcat-embed-core-10.1.31.jar:6.0] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:195 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:140 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.tomcat.websocket.server.WsFilter.doFilter( WsFilter.java:51 ) ~[tomcat-embed-websocket-10.1.31.jar:10.1.31] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:164 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:140 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.springframework.web.filter.RequestContextFilter.doFilterInternal( RequestContextFilter.java:100 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.filter.OncePerRequestFilter.doFilter( OncePerRequestFilter.java:116 ) ~[spring-web-6.1.14.jar:6.1.14] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:164 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:140 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.springframework.web.filter.FormContentFilter.doFilterInternal( FormContentFilter.java:93 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.filter.OncePerRequestFilter.doFilter( OncePerRequestFilter.java:116 ) ~[spring-web-6.1.14.jar:6.1.14] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:164 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:140 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal( CharacterEncodingFilter.java:201 ) ~[spring-web-6.1.14.jar:6.1.14] at org.springframework.web.filter.OncePerRequestFilter.doFilter( OncePerRequestFilter.java:116 ) ~[spring-web-6.1.14.jar:6.1.14] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter( ApplicationFilterChain.java:164 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.ApplicationFilterChain.doFilter( ApplicationFilterChain.java:140 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.StandardWrapperValve.invoke( StandardWrapperValve.java:167 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.StandardContextValve.invoke( StandardContextValve.java:90 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.authenticator.AuthenticatorBase.invoke( AuthenticatorBase.java:483 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:115 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.valves.ErrorReportValve.invoke( ErrorReportValve.java:93 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.core.StandardEngineValve.invoke( StandardEngineValve.java:74 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.catalina.connector.CoyoteAdapter.service( CoyoteAdapter.java:344 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.coyote.http11.Http11Processor.service( Http11Processor.java:384 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.coyote.AbstractProcessorLight.process( AbstractProcessorLight.java:63 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process( AbstractProtocol.java:905 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.tomcat.util.net .NioEndpoint$SocketProcessor.doRun( NioEndpoint.java:1741 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.tomcat.util.net.SocketProcessorBase.run ( SocketProcessorBase.java:52 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1190 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.tomcat.util.threads.ThreadPoolExecutor$ Worker.run ( ThreadPoolExecutor.java:659 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at org.apache.tomcat.util.threads.TaskThread$ WrappingRunnable.run ( TaskThread.java:63 ) ~[tomcat-embed-core-10.1.31.jar:10.1.31] at java.base/ java.lang.Thread.run ( Thread.java:842 ) ~[na:na] 이런 내용으로 출력이 됩니다. 도무지 해결할 방법을 찾이 못해 질문드립니다~
스프링강의 입문 강의를 따라 강의를 학습중입니다. java 20 버전으로 설정해 사용중, 설정과 project structure에 sdk, jdk를 모두 20버전으로 설정하엿습니다. 하지만 화면 과 같은 오류가 떠서 자바 버전을 찾을 수 없다고 하는데. 혹시 어떻게 해결해야 하나여? 밑에는 버전사진입니다.
학습하는 분들께 도움이 되고, 더 좋은 답변을 드릴 수 있도록 질문전에 다음을 꼭 확인해주세요. 1. 강의 내용과 관련된 질문을 남겨주세요. 2. 인프런의 질문 게시판과 자주 하는 질문(링크)을 먼저 확인해주세요. (자주 하는 질문 링크: https://bit.ly/3fX6ygx) 3. 질문 잘하기 메뉴얼(링크)을 먼저 읽어주세요. (질문 잘하기 메뉴얼 링크: https://bit.ly/2UfeqCG) 질문 시에는 위 내용은 삭제하고 다음 내용을 남겨주세요. ========================================= [질문 템플릿] 1. 강의 내용과 관련된 질문인가요? (예/아니오) 예 2. 인프런의 질문 게시판과 자주 하는 질문에 없는 내용인가요? (예/아니오) 있지만 적용이 안 됩니다 3. 질문 잘하기 메뉴얼을 읽어보셨나요? (예/아니오) 예 [질문 내용] <property name="hibernate.hbm2ddl.auto" value="update" /> update 옵션 사용시에 에러가 발생합니다. h2 database는 1.4.200 버전으로 변경 후 사용하고 있습니다 (기존 2.xx) 아무리 찾아봐도.. 이거 해결책이 안 보이는데, 버전이 잘못 되거나 다른 이슈가 있을까요..? 에러 로그는 ``` Exception in thread "main" jakarta.persistence.PersistenceException: [PersistenceUnit: hello] Unable to build Hibernate SessionFactory at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:1591) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1512) at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:55) at jakarta.persistence.Persistence.createEntityManagerFactory(Persistence.java:80) at jakarta.persistence.Persistence.createEntityManagerFactory(Persistence.java:55) at hellojpa.JpaMain.main(JpaMain.java:13) Caused by: org.hibernate.exception.SQLGrammarException: Unable to build DatabaseInformation [Column "start_value" not found [42122-200]] [n/a] at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:66) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94) at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:194) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:98) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:286) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:145) at java.base/java.util.HashMap.forEach(HashMap.java:1429) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:142) at org.hibernate.boot.internal.SessionFactoryObserverForSchemaExport.sessionFactoryCreated(SessionFactoryObserverForSchemaExport.java:37) at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35) at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:315) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:450) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1507) ... 4 more Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "start_value" not found [42122-200] at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) at org.h2.message.DbException.get(DbException.java:205) at org.h2.message.DbException.get(DbException.java:181) at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3169) at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3268) at org.h2.jdbc.JdbcResultSet.getLong(JdbcResultSet.java:680) at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.resultSetStartValueSize(SequenceInformationExtractorLegacyImpl.java:110) at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.lambda$extractMetadata$0(SequenceInformationExtractorLegacyImpl.java:54) at org.hibernate.tool.schema.extract.spi.ExtractionContext.getQueryResults(ExtractionContext.java:50) at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:39) at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:66) at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:60) at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:185) ... 14 more ``` pom.xml 입니다 ``` <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>3.3.4</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>jpa-basic</groupId> <artifactId>ex1-hello-jpa</artifactId> <version>0.0.1-SNAPSHOT</version> <name>ex1-hello-jpa</name> <description>ex1-hello-jpa</description> <url/> <licenses> <license/> </licenses> <developers> <developer/> </developers> <scm> <connection/> <developerConnection/> <tag/> <url/> </scm> <properties> <java.version>21</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- JPA 하이버네이트 --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>6.4.2.Final</version> </dependency> <dependency> <groupId>javax.xml.bind</groupId> <artifactId>jaxb-api</artifactId> <version>2.3.1</version> </dependency> <!-- H2 데이터베이스 --> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.4.200</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project> ```
File, Files의 4페이지 밑에서 세 번째 줄 Path newFile = Paths.get("temp/newExample.txt"); 교안에는 위와 같이 나와있는데, 강의 영상에서는 아래와 같이 코드를 작성해주셨습니다. Path newFile = Path.of("temp/newExample.txt"); 두 코드는 같은 역할을 하는 것인가요??
1. 1byte 씩 전송하면 당연히 시스템콜이 많이 작동하므로 속도가 느리다 2. 8바이트씩 전송하면 시스템 콜에서 어차피 8바이트씩 전송하므로 시스템 콜을 적게 호출 할 수 있다 [질문] 한번에 전송해도 시스템콜에서 8kb바이트씩 보낼텐데 시스템콜 요청하는 횟수가 2번과 비슷할텐데 왜 버퍼를 쓰는게 더 빠르게 나오는 걸까요? 혹시 이게 한번 전송하면 한꺼번에 시스템콜 직전까지 전달하므로 병목현상 뭐 그런걸까요? ++ 강의에서 한번에 써도 os상에서 8kb씩 보내신다고 했던것 같은데 맞나요? 메모리에 한번에 올려서 시스템콜에 가져다 주는 것이 부하가 걸려서 8kb씩 버퍼로 하는 것보다 더 느린걸까요?
[ARRAY, STRUCT] array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, #genres, genre FROM `advanced.array_excercises` ae cross join unnest(genres) as genre; 2. array_exercies 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요 배우와 배역은 별도의 컬럼으로 나와야 합니다. select title, actor.actor, actor.character from `advanced.array_excercises` cross join unnest(actors) as actor; array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. select title, actor.actor, actor.character, genre from `advanced.array_excercises` cross join unnest(actors) as actor cross join unnest(genres) as genre; 앱 로그 데이터(app_logs) 배열 풀기 select user_id, event_date, event_name, user_pseudo_id, params.key, params.value.string_value as string_value, params.value.int_value as int_value from `advanced.app_logs` cross join unnest(event_params) as params where event_date = "2022-08-01 [PIVOT] orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. select order_date, # amount의 합 sum(if(user_id=1, amount, 0)) as user_1, sum(if(user_id=2, amount, 0)) as user_2, sum(if(user_id=3, amount, 0)) as user_3 FROM `advanced.orders` group by order_date order by order_date; orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 select user_id, sum(if(order_date = "2023-05-01", amount, 0)) as `2023-05-01`, sum(if(order_date = "2023-05-02", amount, 0)) as `2023-05-02`, sum(if(order_date = "2023-05-03", amount, 0)) as `2023-05-03`, sum(if(order_date = "2023-05-04", amount, 0)) as `2023-05-04`, sum(if(order_date = "2023-05-05", amount, 0)) as `2023-05-05` from `advanced.orders` group by user_id order by user_id; orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. select user_id, max(if(order_date = "2023-05-01", 1, 0)) as `2023-05-01`, max(if(order_date = "2023-05-02", 1, 0)) as `2023-05-02`, max(if(order_date = "2023-05-03", 1, 0)) as `2023-05-03`, max(if(order_date = "2023-05-04", 1, 0)) as `2023-05-04`, max(if(order_date = "2023-05-05", 1, 0)) as `2023-05-05` from `advanced.orders` group by user_id order by user_id; user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? with base as ( select -- * EXCEPT(event_params), # * except(컬럼) :컬럼을 제외하고 모두 다 보여줘 -- param event_date, event_timestamp, event_name, user_id, user_pseudo_id, max(if(param.key = "firebase_screen", param.value.string_value, null)) as firebase_screen, -- max(if(param.key = "food_id", param.value.string_value, null)) as food_id, max(if(param.key = "food_id", param.value.int_value, null)) as food_id, max(if(param.key = "session_id", param.value.string_value, null)) as session_id from `advanced.app_logs` cross join unnest(event_params) as param group by all ) select user_id, event_date, count(user_id) as user_cnt, food_id from base where user_id = 32888 and event_name = "click_cart" group by all [퍼널분석] with base as ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, max(if(event_param.key = "firebase_screen", event_param.value.string_value, null)) as firebase_screen, max(if(event_param.key = "session_id", event_param.value.string_value, null)) as session_id from `advanced.app_logs` cross join unnest(event_params) as event_param where event_date between "2022-08-01" and "2022-08-18" group by all ), filter_event as( select * except(event_name, firebase_screen, event_timestamp), concat(event_name, "-", firebase_screen) as event_name_with_screen, DATETIME(timestamp_micros(event_timestamp),'Asia/Seoul')AS event_datetime from base where event_name IN("screen_view", "click_payment") ), screen_view as( select event_date, event_name_with_screen, case when event_name_with_screen = "screen_view-welcome" then 1 when event_name_with_screen = "screen_view-home" then 2 when event_name_with_screen = "screen_view-food_category" then 3 when event_name_with_screen = "screen_view-restaurant" then 4 when event_name_with_screen = "screen_view-cart" then 5 when event_name_with_screen = "click_payment-cart" then 6 else null end as step_number, count(distinct user_pseudo_id) as cnt from filter_event group by all having step_number is not null order by event_date ) select event_date, max(if(event_name_with_screen = "screen_view-welcome", cnt, null)) as screen_view_welcome, max(if(event_name_with_screen = "screen_view-home", cnt, null)) as screen_view_home, max(if(event_name_with_screen = "screen_view-food_category", cnt, null)) as screen_view_food_category, max(if(event_name_with_screen = "screen_view-restaurant", cnt, null)) as screen_view_restaurant, max(if(event_name_with_screen = "screen_view-cart", cnt, null)) as screen_view_cart, max(if(event_name_with_screen = "click_payment-cart", cnt, null)) as click_payment_cart from screen_view group by all order by event_date
--연습문제 CREATE OR REPLACE TABLE advanced.array_exercises AS SELECT movie_id, title, actors, genres FROM ( SELECT 1 AS movie_id, 'Avengers: Endgame' AS title, ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Robert Downey Jr.', 'Tony Stark'), STRUCT('Chris Evans', 'Steve Rogers') ] AS actors, ARRAY<STRING>['Action', 'Adventure', 'Drama'] AS genres UNION ALL SELECT 2, 'Inception', ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Leonardo DiCaprio', 'Cobb'), STRUCT('Joseph Gordon-Levitt', 'Arthur') ], ARRAY<STRING>['Action', 'Adventure', 'Sci-Fi'] UNION ALL SELECT 3, 'The Dark Knight', ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Christian Bale', 'Bruce Wayne'), STRUCT('Heath Ledger', 'Joker') ], ARRAY<STRING>['Action', 'Crime', 'Drama'] ) -- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 select title , genres_new from advanced,.array_exercise AS a, UNNEST(genres) as genres_new -- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 select title , actors_new.actor , actors_new.character from advanced,.array_exercise AS a, UNNEST(actors) as actors_new --3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 --방법 1 with gen as ( select title , genres_new from advanced,.array_exercise AS a, UNNEST(genres) as genres_new ) , actors as ( select title , actors_new.actor , actors_new.character from advanced,.array_exercise AS a, UNNEST(actors) as actors_new ) select from gen g join actors a on g.title=a.title --방법 2 select title , actors_new.actor , actors_new.character , genre_new from advanced,.array_exercise AS a, UNNEST(actors) as actors_new, UNNEST(genres) as genre_new --방법 3 select title , actors_new.actor , actors_new.character , genre_new from advanced.array_exercise cross join UNNEST(actors) as actors_new cross join UNNEST(genres) as genre_new where actors_new.actor 로 조건을 걸어야함 --actor(키값바로) 또는 actors_new로는 안된다 actors_new는 스트럭트 구조이고 actor는 이전 값임 --4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 --하루 사용자 집계, 어떤 이벤트가 있는가? select user_id , event_date , event_name , user_pseudo_id , event_component.key , event_component.value.string_value , event_component.value.int_value from app_logs as app, UNNEST(event_pharams) as event_component where event_date = '2022-08-11' --피봇 과제 --1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 with raw as ( select user_id , order_date , sum(amount) as amounts from orders ) SELECT order_date , MAX(IF(user_id=1, amounts, NULL)) AS user_1 , MAX(IF(user_id=2, amounts, NULL)) AS user_2 , MAX(IF(user_id=3, amounts, NULL)) AS user_3 ... FROM raw GROUP BY order_date --2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 with raw as ( --혹시나 하나의 유저가 하루에 여러 주문을 했을수도 있을것 같아서 이것 사용(MAX 쓸 예정이라서) select user_id , order_date , sum(amount) as amounts from orders ) SELECT user_id , MAX(IF(order_date='2023-05-01', amounts, NULL)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', amounts, NULL)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', amounts, NULL)) AS '2023-05-03' ... FROM raw GROUP BY user_id --3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 with raw as ( select user_id , order_date , sum(amount) as amounts , count(distinct order_id) as order_cnt from orders ) SELECT user_id , MAX(IF(order_date='2023-05-01', 1, 0)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', 1, 0)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', 1, 0)) AS '2023-05-03' --second case , MAX(IF(order_date='2023-05-01', order_cnt, 0)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', order_cnt, 0)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', order_cnt, 0)) AS '2023-05-03' ... FROM raw GROUP BY user_id -- 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? -- ##데이터 조회할때 유용한 except(column):특정 컬럼 제외하고 모두 다 -- select * except(event_params) -- GROUP BY ALL with raw as ( select user_id , event_date , event_name , user_pseudo_id , event_component.key , event_component.value.int_value as food_id from app_logs as app, UNNEST(event_pharams) as event_component where user_id = 32888 and event_name = 'click_cart' and event_component.key = 'food_id' ) select distinct event_time --나는 일별로 보고 싶어서 추가 , food_id from raw --성윤님 강의 내용 select user_id , event_name , event_date , event_timestamp --같은일에 중복 주문이 있을까봐서 , MAX(IF(event_component.key='food_id', event_component.value.int_value, NULL)) AS food_id from app_logs as app, UNNEST(event_pharams) as event_component where user_id = 32888 and event_name = 'click_cart' and event_component.key = 'food_id' GROUP BY ALL --알아서 컬럼들 픽 --퍼널 별 유저 수 집계 with raw as ( select user_id , event_date , event_timestamp , event_name , user_pseudo_id , platform , event_component.key , event_component.value.string_value , event_component.value.int_value , MAX(IF(event_component.key = "firebase_screen", event_component.value.string_value, NULL)) AS firebase_screen -- , MAX(IF(event_component.key = "food_id", event_component.value.int_value, NULL)) AS food_id , MAX(IF(event_component.key = "session_id", event_component.value.int_value, NULL)) AS session_id from app_logs as app, UNNEST(event_pharams) as event_component where event_date BETWEEN "2022-08-01" AND "2022-08-18" group by all ) , filter_event_and_concat_event_and_screen AS( SELECT * EXCEPT(event_name, firebase_screen,event_timestamp) , CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen , DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date, step_number 강의 노트 select [0,1,1,2,3,4] as array_practice array<int64>[0,1,3] as array_practice generate_array(1,5,2) generate_date_array('2024-01-01', '2024-02-01', interval 1 week) WITH programming_languages AS ( SELECT "python" AS programming_language UNION ALL SELECT "go" UNION ALL SELECT "scala" ) select array_agg(programming_languages) as output from programming_languages --배열에 접근하기 offset: #0 ordinal: #1 #out of range를 방지하기 위해서 safe_ 추가하기 --사용 예시 select some_numbers[safe_offset(1)] as second_value 컬럼명[safe_offset(가져오고 싶은 위치)] Array(like list): 비슷한 카테고리에 대해 데이터를 저장할때 예시) 메뉴(컬럼): 돼지국밥, 떡볶이, 치킨 Struct(like dict): 다양한 속성에 대해 데이터를 한 컬럼에 다 넣고 싶을때 예시) 주소록(컬럼): 이름, 전화번호,이메일, 생일 등등 SELECT (1,2,3) AS struct_test SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test SELECT struct_test.hi, struct_test.hello FROM ( SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test ) -- UNNEST를 사용해 중첩된 데이터 구조 풀기(평면화, Flatten) WITH example_data AS( SELECT 'kyle' AS name, ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 'Incheon' AS hometown UNION ALL SELECT 'max' AS name, ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 'Seoul' AS hometown UNION ALL SELECT 'yun' AS name, ['Python', 'SQL'] AS preferred_language, 'Incheon' AS hometown ) SELECT name, pref_lang, hometown FROM example_data CROSS JOIN UNNEST(preferred_language) AS pref_lang FROM exaple_data AS a, UNNEST(preferred_language) AS pref_lang --그럼 unnest안에는 array만? struct는? SELECT student , MAX(IF(subject="수학", score, NULL)) AS 수학 , MAX(IF(subject="영어", score, NULL)) AS 영어 , MAX(IF(subject="과학", score, NULL)) AS 과학 FROM Table GROUP BY student ###팁 #같은 단어를 수정할 때,빨리하고 싶은 - 단어를 커서위에 올리고 커맨드 디 범위설정하고 수정하면 일괄수정 -> 인텔리데이에서는 어떻게 하지? #기대하는 아웃풋의 형태를 적어보는것 좋다 -> 쉐어포인트 컬럼에 만들기 프로젝트 시작전 - 어떤 업무를 함에 있어서 흐름을 아는 것이 중요하다(흐름을 모르면 어떤것을 왜 해야하는지 모를 수 있음) - 맥락 -> 목적 -> 퍼널 -> 가설 -> 분석 서비스의 목표 파악(어떤 문제를 해결하려고 하는지) 문제 정의: 핵심 문제 목표 정의 퍼널 정의 -> 우리도 이 데이터가 있는지 물어보기
한번에 쓰는 것과 8kb씩 버퍼를 이용해서 쓰는 것에 대해서 어차피 시스템 콜에서 8kb씩 전송하는데 한번에 가져다 주는 게 더 빨라야 하는 것이라고 인식되는데 어떻게 8kb씩 버퍼로 주는게 더 빠른걸까요? 한번에 주든 8kb씩 버퍼로 주든 시스템 콜에서 8kb씩 전송하는 거면 한번에 주는 게 나은 거 아닌가요!?
1. ARRAY, STRUCT ### 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 # ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때 # ARRAY를 Flatten(평면화) => UNNEST # UNNEST를 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN) # UNNEST(ARRAY_COLUMN) AS 새로운 이름 # SELECT 절에서 새로운 이름으로 사용한다. 기존의 ARRAY_COLUMN은 사용하지 않는다! -- SELECT -- title -- , genre -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(genres) AS genre -- ORDER BY 1, 2 ## 같은 결과를 출력하기 위해 정렬함. -- ; ### 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. # 직접 접근하려면 actors = [ STRUCT(STRING, STRING)] # actors[SAFE_OFFSET(0)].actor # actors[SAFE_OFFSET(0)].character -- SELECT -- title -- , act.actor# AS actor -- , act.character# AS character -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(actors) AS act -- ORDER BY 1 -- ; ### 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. # 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN) -- SELECT -- title -- -- actors, # ARRAY<STRUCT(STRING, STRING)> -- , act.actor# AS actor -- , act.character# AS character -- -- genres # ARRAY<STRING> -- , genre -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(actors) AS act -- CROSS JOIN UNNEST(genres) AS genre -- -- WHERE 1=1 -- -- ## 강의 촬영 시점 이후에 수정된 듯 두 쿼리 모두 오류없이 실행 되는 것 같아요 ! -- -- AND act.actor = "Chris Evans" -- -- AND actor = "Chris Evans" -- ORDER BY 1 -- ; ### 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요. -- SELECT -- user_id -- , event_date -- , event_name -- , user_pseudo_id -- , evt_prm.key AS key -- , evt_prm.value.string_value AS string_value -- , evt_prm.value.int_value AS int_value -- FROM `advanced.app_logs` -- CROSS JOIN UNNEST(event_params) AS evt_prm -- WHERE 1=1 -- AND event_date = "2022-08-01" -- ORDER BY 2 -- ; ### WITH 문 변경 WITH base AS ( SELECT user_id , event_date , event_name , user_pseudo_id , evt_prm.key AS key , evt_prm.value.string_value AS string_value , evt_prm.value.int_value AS int_value FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS evt_prm WHERE 1=1 AND event_date = "2022-08-01" ) SELECT event_date , event_name , COUNT(DISTINCT user_id) AS cnt FROM base GROUP BY ALL ORDER BY cnt DESC 2. PIVOT # 1) orders 테이블에서 유저(user_id)별로 주문금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. -- 기대하는 output의 형태 -- order_date | user_1 | user_2 | user_3 -- PIVOT : MAX(IF(조건, TRUE일 때의 값, FALSE일 때의 값)) AS new_column + GROUP BY -- MAX 대신 집계 함수를 사용할 수도 있음. SUM -- FALSE일 때의 값은 NULL -- SELECT -- order_date -- , SUM(IF(user_id = 1, amount, 0)) AS user_1 -- , SUM(IF(user_id = 2, amount, 0)) AS user_2 -- , SUM(IF(user_id = 3, amount, 0)) AS user_3 -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; # 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요.user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. -- 기대하는 output의 형태 -- user_id | 2023-05-01 | 2023-05-02 | 2023-05-03 | 2023-05-04 | 2023-05-05 -- SELECT -- user_id -- , SUM(IF(order_date="2023-05-01", amount, 0)) AS `2023-05-01` -- , SUM(IF(order_date="2023-05-02", amount, 0)) AS `2023-05-02` -- , SUM(IF(order_date="2023-05-03", amount, 0)) AS `2023-05-03` -- , SUM(IF(order_date="2023-05-04", amount, 0)) AS `2023-05-04` -- , SUM(IF(order_date="2023-05-05", amount, 0)) AS `2023-05-05` -- 컬럼의 이름을 지정할 때, 영어 제외하고 backtick(`) -- ANY_VALUE : 그훕화 할 대상 중에 임의의 값을 선택한다 (NULL을 제외하고). ANY_VALUE에선 나머지 값들이 NULL이거나 확정적으로 값을 기대할 수 있을 때 사용한다! -- ANY_VALUE(IF(order_date="2023-05-01", amount, NULL)) AS `2023-05-01` -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; # 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. -- SELECT -- user_id -- , MAX(IF(order_date="2023-05-01", 1, 0)) AS `2023-05-01` -- , MAX(IF(order_date="2023-05-02", 1, 0)) AS `2023-05-02` -- , MAX(IF(order_date="2023-05-03", 1, 0)) AS `2023-05-03` -- , MAX(IF(order_date="2023-05-04", 1, 0)) AS `2023-05-04` -- , MAX(IF(order_date="2023-05-05", 1, 0)) AS `2023-05-05` -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; ## 앱 로그 PIVOT WITH base AS( SELECT -- * EXCEPT(event_params) # * EXCEPT(column) : 컬럼을 제외하고 다 보여줘! event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(param.key = "fierbase_screen", param.value.string_value, NULL)) AS fierbase_screen -- , MAX(IF(param.key = "food_id", param.value.string_value, NULL)) AS food_id # string_value엔 food_id 값들이 저장되어 있지 않음. , MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id , MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS sessioon_id FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE 1=1 AND event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date , COUNT(user_id) AS user_cnt FROM base WHERE 1=1 AND event_name = "click_cart" -- AND food_id = 1544 GROUP BY event_date 3. 퍼널 연습 문제 # 퍼널 분석 -- 퍼널 데이터 -- 우리가 사용할 이벤트 => 단계 -- - screen_view : welcome, home, food_category, restaurant, cart -- - click_payment -- step_number : 추후에 정렬을 위해 만들 것 -- 사용할 데이터 : 앱 로그 데이터, GA/Firebase => UNNEST => PIVOT -- 기간 : 2022-08-01 ~ 2022-08-18 WITH base as ( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , platform , MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) as firebase_screen , MAX(IF(event_param.key = "food_id", event_param.value.int_value, NULL)) as food_id , MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) as session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) as event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), base2 as ( SELECT * , CONCAT(event_name, "-", firebase_screen) as event_screen FROM base WHERE 1=1 AND event_name IN ("screen_view", "click_payment") ), base3 as ( SELECT event_screen , event_date , CASE WHEN event_screen = "screen_view-welcome" THEN 1 WHEN event_screen = "screen_view-home" THEN 2 WHEN event_screen = "screen_view-food_category" THEN 3 WHEN event_screen = "screen_view-restaurant" THEN 4 WHEN event_screen = "screen_view-cart" THEN 5 WHEN event_screen = "click_payment-cart" THEN 6 ELSE NULL END as step_number , COUNT(DISTINCT user_pseudo_id) as cnt FROM base2 GROUP BY ALL HAVING step_number is not NULL ORDER BY event_date ) SELECT event_date , MAX(IF(base3.event_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome , MAX(IF(base3.event_screen ="screen_view-home", cnt, NULL)) AS screen_vie_home , MAX(IF(base3.event_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category , MAX(IF(base3.event_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant , MAX(IF(base3.event_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart FROM base3 GROUP BY ALL ORDER BY event_date
[ARRAY, STRUCT] 문제 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre 쿼리 결과 1) 문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor 쿼리 결과 2) 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title, actor.actor AS actor, actor.character AS character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre CROSS JOIN UNNEST(actors) AS actor 쿼리 결과 3) 문제 4) 앱 로그 데이터(app_logs) 배열 풀기 SELECT event_date, event_timestamp, event_name, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value, user_id, user_pseudo_id, platform FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date = "2022-08-01" LIMIT 100 쿼리 결과 4) [PIVOT] 문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY ALL ORDER BY order_date 쿼리 결과 1) 문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 SELECT user_id, SUM(IF(order_date = "2023-05-01", amount, 0)) AS `2023-05-01`, SUM(IF(order_date = "2023-05-02", amount, 0)) AS `2023-05-02`, SUM(IF(order_date = "2023-05-03", amount, 0)) AS `2023-05-03`, SUM(IF(order_date = "2023-05-04", amount, 0)) AS `2023-05-04`, SUM(IF(order_date = "2023-05-05", amount, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY ALL ORDER BY user_id 쿼리 결과 2) 문제 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 SELECT user_id, MAX(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`, MAX(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`, MAX(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`, MAX(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`, MAX(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY ALL ORDER BY user_id 쿼리 결과 3) 문제 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(event_param.key = 'firebase_screen',event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = 'food_id',event_param.value.int_value, NULL)) AS food_id, MAX(IF(event_param.key = 'session_id',event_param.value.string_value, NULL)) AS session_id, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param GROUP BY ALL ) SELECT user_id, event_date, COUNT(user_id) AS user_cnt, food_id FROM base WHERE user_id = 32888 and event_name = 'click_cart' GROUP BY ALL 쿼리 결과 4) [퍼널분석] WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL 쿼리 결과
1. ARRAY, STRUCT 1) SELECT title, movie_genres FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS movie_genres LIMIT 100 2) SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor 3) SELECT title, actors.actor, actors.character, genres FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actors CROSS JOIN UNNEST(genres) AS genres WHERE actor = 'ChrisEvans' 4) select user_id , event_date , event_name , user_pseudo_id , param.key as key , param.value.string_value as string_value , param.value.int_value as int_value from advanced.app_logs , unnest(event_params) as param 2. PIVOT 1) select order_date , sum(if(user_id = 1,amount,0)) as user_1 , sum(if(user_id = 2,amount,0)) as user_2 , sum(if(user_id = 3,amount,0)) as user_3 from advanced.orders group by order_date order by order_date 2) select user_id , max(if(order_date = '2023-05-01',amount,0)) as `2023-05-01` , max(if(order_date = '2023-05-02',amount,0)) as `2023-05-02` , max(if(order_date = '2023-05-03',amount,0)) as `2023-05-03` , max(if(order_date = '2023-05-04',amount,0)) as `2023-05-04` , max(if(order_date = '2023-05-05',amount,0)) as `2023-05-05` from advanced.orders group by user_id order by user_id 3) select user_id , max(if(order_date = '2023-05-01',1,0)) as `2023-05-01` , max(if(order_date = '2023-05-02',1,0)) as `2023-05-02` , max(if(order_date = '2023-05-03',1,0)) as `2023-05-03` , max(if(order_date = '2023-05-04',1,0)) as `2023-05-04` , max(if(order_date = '2023-05-05',1,0)) as `2023-05-05` from advanced.orders group by user_id order by user_id 4) WITH base AS ( SELECT user_id, event_date, event_name, user_pseudo_id, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param ) SELECT user_id, event_date, event_name, user_pseudo_id, MAX(IF(key = 'firebase_screen', string_value, NULL)) AS firebase_screen, MAX(IF(key = 'food_id', int_value, NULL)) AS food_id, MAX(IF(key = 'session_id', string_value, NULL)) AS session_id FROM base WHERE user_id = 32888 AND event_name = 'click_cart' GROUP BY ALL 3. 퍼널 1) with base as ( select event_date , event_name , event_timestamp , user_id , user_pseudo_id , platform , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen from advanced.app_logs , unnest(event_params) as param where event_date between '2022-08-01' and '2022-08-18' group by all ), filter_event as ( select * except(event_name, firebase_screen) , concat(event_name, "-", firebase_screen) as event_name_with_screen from base where event_name in ('screen_view', 'click_payment') ) select event_date , event_name_with_screen , case when event_name_with_screen = 'screen_view-welcome' then 1 when event_name_with_screen = 'screen_view-home' then 2 when event_name_with_screen = 'screen_view-food_category' then 3 when event_name_with_screen = 'screen_view-restaurant' then 4 when event_name_with_screen = 'screen_view-cart' then 5 when event_name_with_screen = 'click_payment-cart' then 6 else null end as step_number , count(distinct user_pseudo_id) as cnt from filter_event group by all having step_number is not null order by event_date 2) with base as ( select event_date , event_name , event_timestamp , user_id , user_pseudo_id , platform , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen from advanced.app_logs , unnest(event_params) as param where event_date between '2022-08-01' and '2022-08-18' group by all ), filter_event as ( select * except(event_name, firebase_screen) , concat(event_name, "-", firebase_screen) as event_name_with_screen from base where event_name in ('screen_view', 'click_payment') ), daily_group as ( select event_date , event_name_with_screen , case when event_name_with_screen = 'screen_view-welcome' then 1 when event_name_with_screen = 'screen_view-home' then 2 when event_name_with_screen = 'screen_view-food_category' then 3 when event_name_with_screen = 'screen_view-restaurant' then 4 when event_name_with_screen = 'screen_view-cart' then 5 when event_name_with_screen = 'click_payment-cart' then 6 else null end as step_number , count(distinct user_pseudo_id) as cnt from filter_event group by all having step_number is not null order by event_date ) select event_date , max(if(event_name_with_screen = 'screen_view-welcome',cnt,null)) as screen_view_welcome , max(if(event_name_with_screen = 'screen_view-home',cnt,null)) as screen_view_home , max(if(event_name_with_screen = 'screen_view-food_category',cnt,null)) as screen_view_food_category , max(if(event_name_with_screen = 'screen_view-restaurant',cnt,null)) as screen_view_restaurant , max(if(event_name_with_screen = 'screen_view-cart',cnt,null)) as screen_view_cart , max(if(event_name_with_screen = 'click_payment-cart',cnt,null)) as click_payment_cart from daily_group group by all order by event_date
ARRAY, STRUCT 연습 문제 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(genres) AS genre 각 영화(title)별 배우(actor)와 배역(character)을 보여주세요.(별도 칼럼) SELECT title, actor.actor, actor.character FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS actor 각 영화(title)별로 배우(actor),배역(character),장르(genre)를 출력하세요. SELECT title, actor, character, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre 앱 로그 데이터(app_logs)의 배열을 풀어주세요. SELECT event_date, event_timestamp, event_name, user_pseudo_id, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param PIVOT 연습 문제 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT (날짜를 행, user_id를 열) SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date 날짜별로 유저들의 주문금액의 합계를 PIVOT (user_id를 행, order_date를 열) SELECT user_id, SUM(IF(order_date = '2023-05-01', amount, 0)) AS `2023-05-01`, SUM(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02`, SUM(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03`, SUM(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04`, SUM(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY user_id ORDER BY user_id 사용자별, 날짜별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요 (user_id를 행, order_date를 열) SELECT user_id, IF(SUM(IF(order_date = '2023-05-01', amount, 0))>0,1,0) AS `2023-05-01`, IF(SUM(IF(order_date = '2023-05-02', amount, 0))>0,1,0) AS `2023-05-03`, IF(SUM(IF(order_date = '2023-05-03', amount, 0))>0,1,0) AS `2023-05-02`, IF(SUM(IF(order_date = '2023-05-04', amount, 0))>0,1,0) AS `2023-05-04`, IF(SUM(IF(order_date = '2023-05-05', amount, 0))>0,1,0) AS `2023-05-05` FROM advanced.orders GROUP BY user_id ORDER BY user_id user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? SELECT user_id, MAX(IF(event_param.key = 'firebase_screen',event_param.value.string_value,NULL)) AS firebase_screen, MAX(IF(event_param.key = 'food_id',event_param.value.int_value,NULL)) AS food_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE user_id = 32888 AND event_name = 'click_cart' GROUP BY user_id, event_timestamp -- 카트에 담은 음식(food_id): 1559, 1942 퍼널 쿼리 연습 문제 일자별 이벤트 집계 후 PIVOT WITH funnel_data AS ( SELECT *, CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 END AS step_number FROM ( SELECT event_date, event_timestamp, user_pseudo_id, concat(event_name, '-', event_param.value.string_value) AS event_name_with_screen FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_param.key = 'firebase_screen' ) AS unnested_app_logs WHERE event_name_with_screen IN ( 'screen_view-welcome', 'screen_view-home', 'screen_view-food_category', 'screen_view-restaurant', 'screen_view-cart', 'click_payment-cart' ) )SELECT event_date, COUNT(IF(step_number = 1, user_pseudo_id, NULL)) AS `screen_view-welcome`, COUNT(IF(step_number = 2, user_pseudo_id, NULL)) AS `screen_view-home`, COUNT(IF(step_number = 3, user_pseudo_id, NULL)) AS `screen_view-food_category`, COUNT(IF(step_number = 4, user_pseudo_id, NULL)) AS `screen_view-restaurant`, COUNT(IF(step_number = 5, user_pseudo_id, NULL)) AS `screen_view-cart`, COUNT(IF(step_number = 6, user_pseudo_id, NULL)) AS `click_payment-cart` FROM funnel_data GROUP BY event_date ORDER BY event_date
1. ARRAY, STRUCT 연습문제 문제 1) array_exercise테이블에서 각 영화(title)별로 장르(genres)를 UNNEST 해서 보여주세요 SELECT title, genres FROM `analystic-project.advanced.array_exercises` , UNNEST(genres) AS genres ; 문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야합니다 SELECT title, actors.actor, actors.character FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors ; 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 SELECT title, actors.actor, actors.character, genres FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors, UNNEST(genres) genres ; 문제 4) 앱 로그 데이터(app_logs) 배열 풀기 SELECT user_id, event_date, event_name, user_pseudo_id, pr.key, pr.value.string_value, pr.value.int_value FROM `analystic-project.advanced.app_logs` , UNNEST(event_params) AS pr WHERE event_date = "2022-08-01" LIMIT 1000 ; 2. PIVOT 연습문제 풀이 문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 SELECT order_date, COALESCE(SUM(IF(user_id = 1, amount, null)),0) AS user_1, COALESCE(SUM(IF(user_id = 2, amount, null)),0) AS user_2, COALESCE(SUM(IF(user_id = 3, amount, null)),0) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date ; 문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 SELECT user_id, COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`, COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`, COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`, COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`, COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),0) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id ; 문제 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 SELECT user_id, MAX(IF(order_date = '2023-05-01' AND order_id is not null, 1, 0)) AS `2023-05-01`, MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`, MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`, MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`, MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id ; 문제 4) user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? WITH app_order_raw AS ( SELECT user_id, event_date, event_name, user_pseudo_id, pr.key, pr.value.string_value, pr.value.int_value FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date = '2022-08-01' ) SELECT user_id, event_date, event_name, user_pseudo_id, MAX(IF(key = 'firebase_screen', string_value, null)) AS firebase_screen, MAX(IF(key = 'food_id', int_value, null)) AS food_id, MAX(IF(key = 'session_id', string_value, null)) AS session_id, FROM app_order_raw GROUP BY user_id, event_date, event_name, user_pseudo_id ; 3. 퍼널분석 문제 1) 각 퍼널의 유저 수를 집계 / 데이터 기준: 2022-08-01 ~ 2022-08-18 WITH funnel_data_raw AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name, CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ) SELECT event_name_with_screen, CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel_data_raw WHERE event_name IN ('screen_view', 'click_payment') AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') GROUP BY 1,2 ORDER BY 2 ; 문제 2) 일자별 퍼널 유저 수 집계 WITH funnel_data_raw AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name, CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ) SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel_data_raw WHERE event_name IN ('screen_view', 'click_payment') AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') GROUP BY 1,2,3 ORDER BY 1,3 ; 문제 3) 일자별 퍼널 유저 수 집계 형태를 PIVOT형태로 전환하기 WITH funnel_data_raw AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name, CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ), daily_funnel_user_count as ( SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel_data_raw WHERE event_name IN ('screen_view', 'click_payment') AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') GROUP BY 1,2,3 ORDER BY 1,3 ) SELECT event_date, MAX(IF(step_number = 1, cnt, null)) AS `screen_view-welcome`, MAX(IF(step_number = 2, cnt, null)) AS `screen_view-home`, MAX(IF(step_number = 3, cnt, null)) AS `screen_view-food_category`, MAX(IF(step_number = 4, cnt, null)) AS `screen_view-restaurant`, MAX(IF(step_number = 5, cnt, null)) AS `screen_view-cart`, MAX(IF(step_number = 6, cnt, null)) AS `click_payment-cart`, FROM daily_funnel_user_count GROUP BY ALL ORDER BY 1 ;
<PART 1> ARRAY, STRUCT 연습문제 Q1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. -- 출제의도: 배열 UNNEST의 기본 형태를 사용할 수 있는가? SELECT title , genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre ORDER BY title; Q2. array_exercise 테이블에서 각 영화(title)별로 배우(actors)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. -- 출제의도: 다중 배열 구조에서 UNNEST를 사용할 수 있는가? SELECT title , actor.actor , actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor ORDER BY title; Q3. array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. -- 출제의도: 여러 칼럼을 동시에 UNNEST할 수 있는가? SELECT title , actor.actor , actor.character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre ORDER BY title; Q4. 앱 로그 데이터(app_logs)의 배열을 풀어주세요. -- 출제의도: 다중 struct 구조의 데이터를 평면화하여 쿼리로 호출할 수 있는가? SELECT user_id , event_date , event_name , user_pseudo_id , event.key , event.value.string_value , event.value.int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event WHERE event_date = '2022-08-01'; <PART 2> PIVOT 연습문제 Q1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. -- 출제의도: 집계 함수와 조건 함수를 결합하여 PIVOT 테이블을 만들 수 있는가? SELECT order_date , SUM(IF(user_id=1, amount, 0)) AS user_1 , SUM(IF(user_id=2, amount, 0)) AS user_2 , SUM(IF(user_id=3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY ALL ORDER BY order_date; Q2. orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id 를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. -- 출제의도 : PIVOT 테이블 구성 시, 날짜 칼럼을 이용하여 시계열 방식을 구성할 수 있는가? SELECT user_id , SUM(IF(order_date = '2023-05-01', amount, 0)) AS `2023-05-01` , SUM(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02` , SUM(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03` , SUM(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04` , SUM(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY ALL ORDER BY user_id; Q3. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. -- 출제의도 : PIVOT 테이블 구성 시, 집계 함수로 MAX를 사용할 수 있는가? SELECT user_id , MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01` , MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02` , MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03` , MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04` , MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY ALL ORDER BY user_id; Q4. user_id = 32888 이 카트 추가하기(click_cart)를 누를 때 어떤 음식 (food_id)을 담았는지 구해주세요. key 를 Column 으로 두고, string_value 나 int_value를 Column의 값으로 설정해서 풀어주세요. -- 출제의도 : PIVOT 테이블을 앱로그 데이터에 사용하여, 조건문으로 개별 유저 데이터를 특정할 수 있는가? WITH base AS( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen , MAX(IF(params.key = 'food_id', params.value.int_value, NULL)) AS food_id , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01' GROUP BY ALL ) SELECT * FROM base WHERE event_name = 'click_cart' and user_id = 32888 -- 실행결과 : food_id = 1942 <PART 3> 퍼널 연습문제 -- 출제의도: 앱 로그 데이터에서 원하는 이벤트를 추출해, 퍼널 분석을 위한 전처리를 진행할 수 있는가? -- step 1. UNNEST를 통한 base 데이터 준비 WITH base AS( SELECT event_date , event_timestamp , event_name , event.key AS event_key , event.value.string_value AS event_string_value , event.value.int_value AS event_int_value , user_id , user_pseudo_id , platform FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event WHERE event_date BETWEEN '2022-08-01' AND '2022-08-22' ), -- step 2. 필요한 퍼널 이벤트에만 step_number를 세팅하여 준비 sorted_events AS( SELECT event_date , CONCAT(event_name, "-", event_string_value) AS event_name_with_screen , CASE WHEN event_name = 'screen_view' AND event_string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND event_string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND event_string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND event_string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND event_string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND event_string_value = 'cart' THEN 6 ELSE NULL END AS step_number , user_pseudo_id FROM base WHERE event_key = 'firebase_screen' ) -- step 3. 최종 조회 쿼리 SELECT event_date , event_name_with_screen , step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM sorted_events WHERE step_number IS NOT NULL GROUP BY ALL ORDER BY event_date, step_number
ARRAY -- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre --2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor --3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title, actor.actor AS actor, actor.character AS character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre WHERE actor.actor = 'Chris Evans' AND genre = 'Action'es) AS genre -- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 WITH base AS ( SELECT user_id, event_date, event_name, user_pseudo_id, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date = '2022-08-01') PIVOT --1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어아 합니다. SELECT order_date, MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1, MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2, MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM ( SELECT order_date, user_id, SUM(amount) AS sum_of_amount FROM advanced.orders GROUP BY order_date, user_id ) GROUP BY order_date ORDER BY order_date --2.orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. SELECT user_id, MAX(IF(order_date = "2023-05-01", amount, 0)) AS `2023-05-01`, MAX(IF(order_date = "2023-05-02", amount, 0)) AS `2023-05-02`, MAX(IF(order_date = "2023-05-03", amount, 0)) AS `2023-05-03`, MAX(IF(order_date = "2023-05-04", amount, 0)) AS `2023-05-04`, MAX(IF(order_date = "2023-05-05", amount, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id --3.orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. SELECT user_id, MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01`, MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02`, MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03`, MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04`, MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id 퍼널 WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL 아직 SQL 익숙지 않아서, 강의 들으면서 코드를 이해하려고 했습니다. 얼른 빅쿼리 SQL입문 강의도 다 듣고, 2주차에 더 실력이 올라갔으면 좋겠습니다!
1.ARRAY, STRUCT 연습문제 /* UNNEST를 사용하는 이유 : 중첩된 데이터를 평평하게 만들어 집계 및 분석을 쉽게 하기 위해 UNNEST된 결과를 사용하여 분석을 실행 : 1.프로그래밍 언어 선호도, 2.지역별 언어 선호도 분석을 통해 Action Itme을 도출 : 프로그래밍 강좌를 제공한다면 선호하는 언어 순으로 영상 제작 등 */ SELECT name, pref_lang, hometown FROM example_data CROSS JOIN UNNEST(preferred_language) AS pref_lang; # UNNEST란 장바구니(배열)에 있는 과일(배열의 값)을 모두 다 꺼내는 것 /* 연습문제 1 UNNEST된 결과를 사용하여 분석을 실행 : 1.영화 장르 선호도 분석을 통해 Action Itme을 도출 : 영화 제작사라면 어떤 장르가 선호되는 것을 보고 영화 제작 */ SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre; /* 연습문제 2 UNNEST된 결과를 사용하여 분석을 실행 : X 분석을 통해 Action Itme을 도출 : X */ SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor /* 연습문제 3 UNNEST된 결과를 사용하여 분석을 실행 : 1.배우의 영화 장르 선호도 분석을 통해 Action Itme을 도출 : 영화 제작시 배우의 장르 선호도 확인 후 */ SELECT title, actor.actor, actor.character, genre FROM advanced.array_exercises ,UNNEST(actors) AS actor, UNNEST(genres) AS genre /* 연습문제 4 */ WITH base AS ( SELECT user_id, event_date, event_name, user_pseudo_id, event_param.key AS key, -- event_param.value AS value, event_param.value.string_value, event_param.value.int_value FROM advanced.app_logs AS al CROSS JOIN UNNEST(event_params) AS event_param WHERE 1=1 AND event_date = '2022-08-01' ) SELECT event_date, event_name, COUNT(DISTINCT user_id) AS cnt FROM base GROUP BY ALL ORDER BY cnt DESC 2.PIVOT 연습문제 SELECT order_date, MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1, MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2, MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM ( SELECT order_date, user_id, #Amount의 합 SUM(amount) AS sum_of_amount FROM advanced.orders GROUP BY order_date, user_id ) GROUP BY order_date ORDER BY order_date; SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date; SELECT order_id, order_date, user_id, IF(order_date = '2023-05-01', amount, NULL) AS `2023-05-01`, IF(order_date = '2023-05-02', amount, NULL) AS `2023-05-02`, IF(order_date = '2023-05-03', amount, NULL) AS `2023-05-03`, IF(order_date = '2023-05-04', amount, NULL) AS `2023-05-04`, IF(order_date = '2023-05-05', amount, NULL) AS `2023-05-05` FROM advanced.orders; SELECT user_id, # amount 대신 1이라고 표시. IF 문 안에 TRUE 일 때의 값이 항상 특정 컬럼이 아니라 1이라고 할 수도 있음(유무에 따라서) MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01`, MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02`, MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03`, MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04`, MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY user_id; WITH base AS ( SELECT # * EXCEPT(event_params), # * EXCEPT(컬럼) : 컬럼을 제외하고 모두 다 보여줘! event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen, MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id2, MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS param WHERE event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name = "click_cart" GROUP BY event_date 3.퍼널 분석 연습문제 WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE 1=1 AND event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) #일자별로 퍼널별 유저 수 쿼리 SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date
