SQL GROUPING SETS усредняет с несколькими измерениями многие-ко-многим
У меня есть таблица данных со следующим:
User,Platform,Dt,Activity_Flag,Total_Purchases
1,iOS,05/05/2016,1,1
1,Android,05/05/2016,1,2
2,iOS,05/05/2016,1,0
2,Android,05/05/2016,1,2
3,iOS,05/05/2016,1,1
3,Android,06/05/2016,1,3
1,iOS,06/05/2016,1,2
4,Android,06/05/2016,1,2
1,Android,06/05/2016,1,0
3,iOS,07/05/2016,1,2
2,iOS,08/05/2016,1,0
Я хочу выполнить агрегацию GROUPING SETS (Platform,Dt,(Platform,Dt),()), чтобы можно было найти для каждой комбинации Platform и Dt следующее:
- Всего покупок
- Всего уникальных пользователей
- Среднее количество покупок на пользователя в день
Первые два просты, так как они могут быть достигнуты через сумму (Total_Purchases) и количество (отдельный пользователь) соответственно.
У меня проблема с последней метрикой. Набор результатов должен выглядеть следующим образом, но я не знаю, как правильно рассчитать последний столбец:
Platform,Dt,Total_Purchases,Total_Unique_Users,Average_Purchases_Per_User_Per_Day
Android,05/05/2016,4,2,2.0
iOS,05/05/2016,2,3,0.7
Android,06/05/2016,5,3,1.7
iOS,06/05/2016,2,1,2.0
iOS,07/05/2016,2,1,2.0
iOS,08/05/2016,0,1,0.0
,05/05/2016,6,3,2.0
,06/05/2016,7,3,2.3
,07/05/2016,1,1,1.0
,08/05/2016,1,1,1.0
Android,,9,4,1.8
iOS,,6,3,1.2
,,15,4,1.6
Для первых десяти строк мы видим, что получение среднего количества покупок на пользователя в день - это простое деление первых двух столбцов, поскольку измерение в этих строках представляет только одну дату. Но когда мы смотрим на последние 3 строки, мы видим, что разделение не является способом достижения желаемого результата. Это связано с тем, что для получения общей суммы за день необходимо брать среднее значение за каждый день.
Если это не ясно, пожалуйста, дайте мне знать, и я буду рад объяснить лучше. Это мой первый пост на этом сайте!