????6. ?????????洢????????????????
??????????Unix??????????????????洢???????????????????????????????????????SQL?????????????????????????????Щ???????????????????????????????????????????????????漰????timestamp??datetime????????????????SQL?????????
????select date(from_unixtime(created_at)) from packages
????-- vs
????select date(created_at) from packages
??????????????????洢??????????С????????????????й???????е???????????д?????????????????????????????????????SQL?????????????
????select date(created_year || '-' || created_month || '-' || created_day)
????-- vs
????select date(created_at)
????7. ???????UTC
????????????????UTC???????????????????????????????????Periscope???????????????UTC?????????????????????й??????Periscope?У???????:pst?????UTC???????????
????select [created_at:pst]?? email_addressfrom users
??????y?????????????UTC??????????datetime???ж?????????????????????磬???????timestamp????
???????????????????????UTC??????????????????UTC???UTC?????????????????е?????????????????????
????8.??????????
????????????????е???????????Source of Truth?????????????Rollup????????????????????????????????????????????????????????????????????????
????select *from daily_usage_rollup
??????????棬??????user_id??user_id_old????user_id_v2???????ж??????????????????????????????????????????л?????????????????????????ε??????
????9.??????????JSON?е???
???????????й??????????????г?????????в????????Щ?????????????????磬answer1??answer2??answer3?????????????????е????ù????
?????????????????????????????????????е???????????????????????????????磬?????????м?????????????????????????????
????select
????sum(
????(case when answer1 is not null
????then 1 else 0 end) +
????(case when answer2 is not null
????then 1 else 0 end) +
????(case when answer3 is not null
????then 1 else 0 end)
????) as num_answers
????from surveys
????where id = 123
????-- vs
????select count(response)
????from answers
????where survey_id = 123
?????????????????????JSON?г????????????????????????????????к?????????????????????????JSON?У????????????????????????????????y?JSON?в????????????????????????????????????????
????10?????????淶??
????????????????????????????????????????????淶?????????????????涼??????Щ?????????????????????????????????????SQL??????????????????????????????
????select
????dates.d??
????count(1)
????from users
????join dates on users.created_date_id = dates.id
????group by 1
????-- vs
????select
????date(created_at)??
????count(1)
????from users
????group by 1
??????????????е?????????к??????????????????????κ????????????????????????????????С?
?????????????????? ????????????????????????3????????????Щ????????????????????????????????????????????????и????????飬??????????hello@periscope.io??????????????????????