Scènes

Pour utiliser l'application, nous souhaitons absolument que les utilisateurs utilisent notre application à chaque fois, plus la fréquence de l'utilisation de l'application, plus active, plus active, plus elle fonctionne, peut également dire une activité élevée. Pour les personnes actives, pour les utilisateurs actifs, ils n'ont pas à dépenser trop d'énergie pour effectuer leurs mouvements. Pour des opérations modérées et même d'occasion atterrir, ils doivent passer des efforts pour les garder. Par conséquent, l'opération est un indice très important pour développer notre stratégie positive.

Pour Courrier, comment évaluer ses efforts, nous pouvons mesurer le nombre de dates de fréquentation en une semaine ou par mois.
Pour les supermarchés, il a peur des clients d'acheter des articles, mais il n'y a pas de marchandises sur l'étagère. Pour de grands supermarchés, il y a beaucoup de suppléments, nous pouvons utiliser le maximum quotidien pour mesurer l'urgence de l'addition. En fait, même quand en rupture de stock, ilPas nécessairement besoin, mais les gens pensent que nos biens ont été achetés tous les jours.

Trois sur les scripts ci-dessus, nous pouvons résoudre un problème pour calculer le nombre de succès dans une certaine période.

Après cela, simplifiez le problème, voir le haut des jours fériés suivants:


Mise en œuvre
Déclaration 1

La clé de ce problème est également regroupée en vacances continues afin que nous puissions utiliser Max min pour effacer le début des vacances.

Row_Number est organisé à la journée, créant un numéro de série qui augmente progressivement puis effectue le classement intérieur et à l'extérieur des vacances sous Is_holaday. Les conclusions suivantes peuvent être prises. Dans lequel D2 - D1 = 1 et D1, D2 sont toutes des vacances.

Non difficile de voir N -1 - K -1 = N -K, N -2-K -2 = N -K, de sorte que D1 et D2 sont placés dans le même groupe. with a as ( select * from ( select '2014-01-01' as date_ , '1' as is_holaday union all select '2014-01-02' as date_ , '0' as is_holaday union all select '2014-01-03' as date_ , '0' as is_holaday union all select '2014-01-04' as date_ , '1' as is_holaday union all select '2014-01-05' as date_ , '1' as is_holaday union all select '2014-01-06' as date_ , '0' as is_holaday union all select '2014-01-07' as date_ , '0' as is_holaday union all select '2014-01-08' as date_ , '1' as is_holaday union all select '2014-01-09' as date_ , '0' as is_holaday union all select '2014-01-10' as date_ , '0' as is_holaday union all select '2014-01-11' as date_ , '1' as is_holaday union all select '2014-01-12' as date_ , '1' as is_holaday union all select '2014-01-13' as date_ , '0' as is_holaday union all select '2014-01-14' as date_ , '0' as is_holaday union all select '2014-01-15' as date_ , '0' as is_holaday union all select '2014-01-16' as date_ , '0' as is_holaday union all select '2014-01-17' as date_ , '0' as is_holaday union all select '2014-01-18' as date_ , '1' as is_holaday union all select '2014-01-19' as date_ , '1' as is_holaday union all select '2014-01-20' as date_ , '1' as is_holaday )) select date_, is_holaday, group_id , if(is_holaday = '0', null, min(date_) over (partition by group_id)) as min_date, if(is_holaday = '0', null, max(date_) over (partition by group_id)) as max_datefrom ( select date_ ,is_holaday , if(is_holaday='1',row_number() over (order by date_ asc)-rank() over (partition by is_holaday order by date_),0) as group_id from a ) as x order by date_

Il y a aussi un problème:
réelN 3

Pour tester mon père pour augmenter la difficulté, certaines données ci-dessous, non seulement pour supprimer la date de début et de fin du nombre de jours continus, mais nécessitent également une demande inter-demandeur de plus de trois jours.

La solution est la suivante:

Ce n'est que lorsque le temps commence et se termine with a as (select * from ( select '2014-01-01' as date_ , '1' as is_holaday union all select '2014-01-02' as date_ , '0' as is_holaday union all select '2014-01-03' as date_ , '0' as is_holaday union all select '2014-01-04' as date_ , '1' as is_holaday union all select '2014-01-05' as date_ , '1' as is_holaday union all select '2014-01-06' as date_ , '0' as is_holaday union all select '2014-01-07' as date_ , '0' as is_holaday union all select '2014-01-08' as date_ , '1' as is_holaday union all select '2014-01-09' as date_ , '0' as is_holaday union all select '2014-01-10' as date_ , '0' as is_holaday union all select '2014-01-11' as date_ , '1' as is_holaday union all select '2014-01-12' as date_ , '1' as is_holaday union all select '2014-01-13' as date_ , '0' as is_holaday union all select '2014-01-14' as date_ , '0' as is_holaday union all select '2014-01-15' as date_ , '0' as is_holaday union all select '2014-01-16' as date_ , '0' as is_holaday union all select '2014-01-17' as date_ , '0' as is_holaday union all select '2014-01-18' as date_ , '1' as is_holaday union all select '2014-01-19' as date_ , '1' as is_holaday union all select '2014-01-20' as date_ , '1' as is_holaday )) , bb as (select date_ ,is_holiday ,if(is_holiday='1' and (last_holiday is null or last_holiday = '0'),1,0) as start_holiday ,if(is_holiday='1' and (next_holiday is null or next_holiday = '0'),1,0) as end_holiday from (select date_ ,is_holaday as is_holiday ,lag(is_holaday) over( order by date_) as last_holiday ,lead(is_holaday) over( order by date_) as next_holiday from a ) as aa )select date_ ,is_holiday ,start_date ,if(is_holiday = '0','' , end_date) as end_date from (select ee.date_ ,ee.is_holiday ,ee.start_date ,dd.date_ as end_date ,row_number() over(partition by ee.date_ ) as index_ from (select date_ ,is_holiday ,if(is_holiday = '0','' , start_date) as start_date from (select bb.date_ ,cc.date_ as start_date ,bb.is_holiday ,row_number() over(partition by bb.date_ order by cc.date_ desc) as index from bb cross join ( select * from bb where start_holiday = 1 ) as ccwhere bb.date_ >= cc.date_order by bb.date_)where index = 1) as ee cross join (select * from bb where end_holiday = 1) as dd where ee.date_ <= dd.date_ )where index_ = 1 order by date_

dans lequel le nom_ utilisateur est le Nombre d'employés, Time_Type est l'identifiant de début et de fin, TS représente l'horodatage.

Retard (champ, temps, défaute_expression) Supprimer la valeur du champ correspondant à l'enregistrement actuel, de plomb (champ, heure, défuutt_value) n'est que l'inverse.? Comme indiqué dans l'image ci-dessous, regardez le haut de la page. jusqu'à la tête (plomb), la latence est la latence (en arrière).

select * from ( select * from ( select 'A' as shop,'2017-10-11' as day,300 as amt union all select 'A' as shop,'2017-10-12' as day , 200 as amt union all select 'B' as shop,'2017-10-11' as day , 400 as amt union all select 'B' as shop,'2017-10-12' as day , 200 as amt union all select 'A' as shop,'2017-10-13' as day , 100 as amt union all select 'A' as shop,'2017-10-15' as day , 100 as amt union all select 'C' as shop,'2017-10-11' as day , 350 as amt union all select 'C' as shop,'2017-10-15' as day , 400 as amt union all select 'C' as shop,'2017-10-16' as day , 200 as amt union all select 'D' as shop,'2017-10-13' as day , 500 as amt union all select 'E' as shop,'2017-10-14' as day , 600 as amt union all select 'E' as shop,'2017-10-15' as day , 500 as amt union all select 'D' as shop,'2017-10-14' as day , 600 as amt union all select 'B' as shop,'2017-10-13' as day , 300 as amt union all select 'C' as shop,'2017-10-17' as day , 100 as amt union all select 'G' as shop,'2017-10-31' as day , 100 as amt union all select 'G' as shop,'2017-11-01' as day , 100 as amt union all select 'G' as shop,'2017-11-02' as day , 100 as amt ) order by shop , day desc Si vous souhaitez supprimer la première étape de l'heure de début en continu, vous devez utiliser la latence pour voir que le journal doit être la fin du logo, mais à son tour, si vous souhaitez obtenir la première fée de données. de toutes les extrémités, cIl est possible que Time_Type soit la fin, auparavant la première partie.

Si vous souhaitez trouver la dernière section, vous pouvez utiliser le plomb.
select * , first_value(day) over(partition by shop order by day) as first_day , first_value(day) over(partition by shop order by day desc ) as first_day from ( select * ,count(1) over(partition by shop , plus ) as coutinues_plus from ( select * -- 看到这里，这里是一个点睛之笔，比 row_number() - rank() 的做法有好多了。 -- 这样可以适用于日期中有断开点的，不连续的 ,date_diff('day' , date('2017-01-01') , date(day)) + row_number() over(partition by shop order by day desc ) as plus from ( select * from ( select 'A' as shop,'2017-10-11' as day,300 as amt union all select 'A' as shop,'2017-10-12' as day , 200 as amt union all select 'B' as shop,'2017-10-11' as day , 400 as amt union all select 'B' as shop,'2017-10-12' as day , 200 as amt union all select 'A' as shop,'2017-10-13' as day , 100 as amt union all select 'A' as shop,'2017-10-15' as day , 100 as amt union all select 'C' as shop,'2017-10-11' as day , 350 as amt union all select 'C' as shop,'2017-10-15' as day , 400 as amt union all select 'C' as shop,'2017-10-16' as day , 200 as amt union all select 'D' as shop,'2017-10-13' as day , 500 as amt union all select 'E' as shop,'2017-10-14' as day , 600 as amt union all select 'E' as shop,'2017-10-15' as day , 500 as amt union all select 'D' as shop,'2017-10-14' as day , 600 as amt union all select 'B' as shop,'2017-10-13' as day , 300 as amt union all select 'C' as shop,'2017-10-17' as day , 100 as amt union all select 'G' as shop,'2017-10-31' as day , 100 as amt union all select 'G' as shop,'2017-11-01' as day , 100 as amt union all select 'G' as shop,'2017-11-02' as day , 100 as amt ) order by shop , day desc ) ))where coutinues_plus >= 3