Description de la scène

La valeur moyenne de N Jour de vente plus tôt.
Premières statistiques supérieures N
Groupe aléatoire

La valeur moyenne des ventes N Jour antérieur

J'utilise ce temps SQL fonctionne sur le Presto , si vous souhaitez exécuter sur la ruche ou d'autres plates-formes, allumez SQL sur le SQL correspondant.

Voir la fonction Array_Agg () sur ().

select leader , employee , array_agg(employee) over(partition by leader order by employee) from ( select 'A' as leader , 'E1' as employeeunion all select 'A' as leader , 'E2' as employeeunion all select 'A' as leader , 'E3' as employeeunion all select 'B' as leader , 'E4' as employeeunion all select 'B' as leader , 'E5' as employeeunion all select 'B' as leader , 'E6' as employee ) as a Les résultats de calcul sont les suivants:

Suivant est quelques fonctions simples:
Inverser, selon le Dictionnaire Orange Ordres Représentant

Slice: Slice: Sortez Le sous-ensemble de matrices, par exemple, des tranches (matrices, start_index, end_index)

Pour plus de descriptions d'images, l'exemple suivant que nous utilisons sera une description.


Les résultats du SQL ci-dessus sont les suivants: select sale_date , shop_id , array_agg(ARRAY[sale_date , shop_id , cast(sale_amt as varchar)]) over(partition by shop_id order by sale_date) from ( select '2019-06-06' as sale_date , 'shop1' as shop_id , 10 as sale_amt union all select '2019-06-05' as sale_date , 'shop1' as shop_id , 14 as sale_amt union all select '2019-06-03' as sale_date , 'shop1' as shop_id , 17 as sale_amt union all select '2019-06-02' as sale_date , 'shop1' as shop_id , 18 as sale_amt union all select '2019-06-01' as sale_date , 'shop1' as shop_id , 13 as sale_amt union all select '2019-06-06' as sale_date , 'shop2' as shop_id , 11 as sale_amt union all select '2019-06-05' as sale_date , 'shop2' as shop_id , 15 as sale_amt union all select '2019-06-03' as sale_date , 'shop2' as shop_id , 18 as sale_amt union all select '2019-06-02' as sale_date , 'shop2' as shop_id , 19 as sale_amt union all select '2019-06-01' as sale_date , 'shop2' as shop_id , 16 as sale_amt ) as a

Faites attention à l'observation des nombres dans la matrice. Nous pouvons déjà l'observer une chaîne. Mais ce dont nous avons besoin est inversé. C'est un problème.

De plus, je souhaite simplement récupérer 3 jours de résumé des données, nous devons donc prendre le sous-ensemble du tableau. C'est un problème.

Après cela, nous rencontrerons un problème, si le jour n'est pas continu, par exemple, Shop2 n'a aucune donnée en 2019-06-04, nous devons donc filtrer des éléments dans le tableau.

Après filtrage, nous avons besoin de plus de ventes dans le tableau.
Préparez-nous pour utiliser la fonctionnalité d'inversion, les fonctions de tranche prennent le sous-ensemble du groupe, filtrant (matrice, Lamda) filtrent les éléments dont nous avons besoin, puis utilisez REDUU pour ajouter des ventes.

Les réductions utilisées ici ne sont pas affichées sur le site Web officiel, diminue (tableaux, moulages (0, 0,00) comme des marchandises (CNT INT, AMT DOBEL), (X, Y) - & GT; Cast; (Like Goods (CNT INT, AMT DOUBLE), S - S - & GT; S)
Ce sont toutes des opérations sur Vue: HTTPS: // PRESTODB .GITUB.IO / DOCS / CIVER /FATIONS/ARRAY.HTML

Vous pouvez obtenir d'abord des filtres clairs et réduire la fonctionnalité.

Sélectionnez Cast (réduit (matrice [1, 2, 3, 4, 5], X - & GT; x & gt; = 4), moulage (rangée (0) comme des marchandises (CNT INT)), (x, y) - & gt; moulage (rangée (y + x.cnt) telle que les marchandises (CNT INT)), x - & gt; x) au format JSON) en RS

Activités, filtre (Array, filtre (x): le premier paramètre est un tableau à utiliser, la seconde est la condition de filtrage, il s'agit d'une expression normale.

Réduction (tableau, lettres initiales, entrée (S, T, S), fonctionnelle. Fonction (S, R))

Le résultat final
Dans la compréhension de la matrice après de nombreuses fonctions, je suis le résultat est affiché ci-dessous
Le résultat final est :

Dans laquelle JSON_ARRAY_GET (JSON_STR, INDEX) Tournera JSON_STR vers JSON_STR vers JSON_OBJECT et SUPPRIMER TIMES TIMES TIMES.

En fait, nous pouvons également utiliser des fonctions de décalage ou de direction à effectuer cette fonction. La solution finale est la suivante:

n
select sale_date , shop_id , sale_pre3 , json_array_get(cast(reduce(filter(array_amt,x -> x[1] >= sale_pre3),cast(ROW(0) as ROW(cnt int)), (x,y) ->cast( ROW(cast(y[3] as integer) + x.cnt) as ROW(cnt int ) ), x -> x ) as json),0) as rs from (select sale_date , shop_id , cast(date_add('day',-2,date(sale_date)) as varchar) as sale_pre3 , array_agg(ARRAY[sale_date , shop_id , cast(sale_amt as varchar)]) over(partition by shop_id order by sale_date) as array_amt from ( select '2019-06-06' as sale_date , 'shop1' as shop_id , 10 as sale_amt union all select '2019-06-05' as sale_date , 'shop1' as shop_id , 14 as sale_amt union all select '2019-06-03' as sale_date , 'shop1' as shop_id , 17 as sale_amt union all select '2019-06-02' as sale_date , 'shop1' as shop_id , 18 as sale_amt union all select '2019-06-01' as sale_date , 'shop1' as shop_id , 13 as sale_amt union all select '2019-06-06' as sale_date , 'shop2' as shop_id , 11 as sale_amt union all select '2019-06-05' as sale_date , 'shop2' as shop_id , 15 as sale_amt union all select '2019-06-03' as sale_date , 'shop2' as shop_id , 18 as sale_amt union all select '2019-06-02' as sale_date , 'shop2' as shop_id , 19 as sale_amt union all select '2019-06-01' as sale_date , 'shop2' as shop_id , 16 as sale_amt ) as a ) as b a d'abord dit que nous savons d'abordN PREMIER AVANT DE PREMIER JOURNÉE N.
Quel est le premier pays N? Revenons à la période étudiante. À cette époque, le directeur dit que les étudiants ont déclaré que les étudiants ont travaillé dur et ils avaient une récompense. Après quelques réflexions, les enseignants de l'Office des questions universitaires ont créé une série d'incitations, l'une d'elles est un "trois corps" à l'étude par chaque classe d'étudiants. Si vous êtes enseignant au bureau des problématiques académiques, devriez-vous prendre ce lot d'étudiant? En fait, certaines plates-formes SQL devraient avoir des idées. Comme indiqué ci-dessous:
Cependant, certains enseignants de classe offrent des points différents, certains d'entre eux, seules 10 personnes sont trop petites, pour suivre chaque type de 10% de gens. Cela semble assez raisonnable.
Alors, comment écrire ceci?
select sale_date , shop_id , ( sale_amt +if(_last_3_day < last_1_day,last_1_amt,0) +if(_last_3_day < last_2_day,last_1_amt,0) +if(_last_3_day < last_3_day,last_1_amt,0) ) as last_3_amt_sum from ( select sale_date , shop_id , sale_amt , cast(date(date_add('day' , cast(-3 as bigint) , cast (sale_date as timestamp))) as varchar) as _last_3_day , lag(sale_amt,1) over(partition by shop_id order by sale_date) as last_1_amt , lag(sale_date,1) over(partition by shop_id order by sale_date) as last_1_day , lag(sale_amt,2) over(partition by shop_id order by sale_date) as last_2_amt , lag(sale_date,2) over(partition by shop_id order by sale_date) as last_2_day , lag(sale_amt,3) over(partition by shop_id order by sale_date) as last_3_amt , lag(sale_date,3) over(partition by shop_id order by sale_date) as last_3_day from ( select '2019-06-06' as sale_date , 'shop1' as shop_id , 10 as sale_amt union all select '2019-06-05' as sale_date , 'shop1' as shop_id , 14 as sale_amt union all select '2019-06-03' as sale_date , 'shop1' as shop_id , 17 as sale_amt union all select '2019-06-02' as sale_date , 'shop1' as shop_id , 18 as sale_amt union all select '2019-06-01' as sale_date , 'shop1' as shop_id , 13 as sale_amt union all select '2019-06-06' as sale_date , 'shop2' as shop_id , 11 as sale_amt union all select '2019-06-05' as sale_date , 'shop2' as shop_id , 15 as sale_amt union all select '2019-06-03' as sale_date , 'shop2' as shop_id , 18 as sale_amt union all select '2019-06-02' as sale_date , 'shop2' as shop_id , 19 as sale_amt union all select '2019-06-01' as sale_date , 'shop2' as shop_id , 16 as sale_amt ) as a) as aa
Emprunter la route classique de Xing Chi "Vous me permettez de jouer au chien, je dois me donner un bâton." Nous devons d'abord connaître le nombre total de personnes dans chaque classe.

à SQL Réécrivez:

La matrice utilisée ci-dessus, nous pouvons utiliser une matrice de tissu de laine? La réponse est oui.
select stu_name , class , score from ( select stu_name , class , score , row_number() over(partition by class order by score desc) as student_ranking from ( select 'a' as stu_name, 'C1' as class 213 as score union all select 'b' as stu_name, 'C1' as class 223 as score union all select 'c' as stu_name, 'C1' as class 223 as score union all select 'd' as stu_name, 'C1' as class 223 as score union all select 'e' as stu_name, 'C1' as class 223 as score union all select 'g' as stu_name, 'C2' as class 213 as score union all select 'h' as stu_name, 'C2' as class 223 as score union all select 'm' as stu_name, 'C2' as class 223 as score union all select 'n' as stu_name, 'C2' as class 223 as score union all select 'i' as stu_name, 'C2' as class 223 as score ) as school_report_card) as school_report_card1where student_ranking <= 10 Je ne sais pas si vous avez un clic de clics, veuillez mettre fin à l'utilisation de clics pour effectuer Nivic Top N.

Clickhouse n'ouvre pas la fenêtre comme Presto, il n'y a donc que cela ne peut que être traité à l'aide d'un tableau.

Premièrement, de voir certaines fonctions de base du tableau

select 'C1' as class , 40 as populationunion all select 'C2' as class , 50 as population Le résultat est le suivant:

Le fruit ci-dessus peut être vu dans La fonction de grouparray (élément de journal) et fonction

de PRESTO'S ARRAY_AGG (). Il placera chaque élément dans l'emballage dans un tableau. select stu_name , class , score , student_ranking from ( select stu_name , class , score , row_number() over(partition by class order by score desc) as student_ranking from ( select 'a' as stu_name, 'C1' as class ,213 as score union all select 'b' as stu_name, 'C1' as class ,223 as score union all select 'c' as stu_name, 'C1' as class ,223 as score union all select 'd' as stu_name, 'C1' as class ,223 as score union all select 'e' as stu_name, 'C1' as class ,223 as score union all select 'g' as stu_name, 'C2' as class ,213 as score union all select 'h' as stu_name, 'C2' as class ,223 as score union all select 'm' as stu_name, 'C2' as class ,223 as score union all select 'n' as stu_name, 'C2' as class ,223 as score union all select 'i' as stu_name, 'C2' as class ,223 as score ) as school_report_card ) as school_report_card1left join ( select 'C1' as class , 40 as populationunion all select 'C2' as class , 50 as population) as class_populationon school_report_card1.class = class_population.classwhere student_ranking <= population*0.1
Ensuite, regardez-la, voir une fonction importante: ArrayResize.

est un exemple que nous savons que le redimensionnement (matrice, new_size) est la taille de la matrice, si NEW_SIZE & GT; Old_size peut être "" ou 0 matrices de remplissage.
Si new_size & lt; Old_Size supprimera certains éléments de la queue.
select class , groupArray(4)([stu_name , cast(score as String)]) from ( select 'a' as stu_name, 'C1' as class ,213 as scoreunion all select 'b' as stu_name, 'C1' as class ,223 as scoreunion all select 'c' as stu_name, 'C1' as class ,212 as scoreunion all select 'd' as stu_name, 'C1' as class ,877 as scoreunion all select 'e' as stu_name, 'C1' as class ,665 as scoreunion all select 'g' as stu_name, 'C2' as class ,6564 as scoreunion all select 'h' as stu_name, 'C2' as class ,444 as scoreunion all select 'm' as stu_name, 'C2' as class ,111 as scoreunion all select 'n' as stu_name, 'C2' as class ,222 as scoreunion all select 'i' as stu_name, 'C2' as class ,333 as score )group by class Après cela, cette fonction est comme ArrayConCat, mais en fait, la différence est particulièrement importante.

Arrayjoin placent des éléments divisés dans la matrice sur la colonne et cette fonction est un peu similaire au côté de la nid d'abeille.

Il existe également un indice de fonction (élément, réseau), renvoyant l'élément pour la position dans la matrice afin que nous puissions obtenir le classement du score.

Suivant, parlez, utilisez Arrayjoin pour développer le catalogue de matrices pour afficher différents champs.

Donc, le résultat final est le suivant:

select class , arrayResize(a.array_group , b.population) as resized_array_group from ( select class , groupArray(4)([stu_name , cast(score as String)]) as array_group from ( select 'a' as stu_name, 'C1' as class ,213 as score union all select 'b' as stu_name, 'C1' as class ,223 as score union all select 'c' as stu_name, 'C1' as class ,212 as score union all select 'd' as stu_name, 'C1' as class ,877 as score union all select 'e' as stu_name, 'C1' as class ,665 as score union all select 'g' as stu_name, 'C2' as class ,6564 as score union all select 'h' as stu_name, 'C2' as class ,444 as score union all select 'm' as stu_name, 'C2' as class ,111 as score union all select 'n' as stu_name, 'C2' as class ,222 as score union all select 'i' as stu_name, 'C2' as class ,333 as score ) group by class ) as a allleft join ( select 'C1' as class , 4 as populationunion all select 'C2' as class , 3 as population) as b on a.class = b.class

Le problème avec un ensemble aléatoire est comme celui-ci, si nous avons 100 étudiants ayant la même chose que l'autre, nous pouvons aller au hasard à deux classes.

Il convient de noter que cet exemple est efficace pour le nombre total de même nombre, mais il y aura un problème lorsque des nombres impairs et ces personnes peuvent le gérer.
Par exemple:

peut également être emballé.Veuillez consulter la formule ci-dessous.

Groupe = index% goup_size

% est effectué dans une opération, un exemple: select aa.class ,arrayConcat(joined_array ,[toString(element_index)] ) from ( select class , arrayJoin(arrayResize(a.array_group , b.population)) as joined_array , indexOf(arrayResize(a.array_group , b.population) ,arrayJoin(arrayResize(a.array_group , b.population))) as element_index from ( select class , groupArray(4)([stu_name , cast(score as String)]) as array_group from ( select * from ( select 'a' as stu_name, 'C1' as class ,213 as score union all select 'b' as stu_name, 'C1' as class ,223 as score union all select 'c' as stu_name, 'C1' as class ,212 as score union all select 'd' as stu_name, 'C1' as class ,877 as score union all select 'e' as stu_name, 'C1' as class ,665 as score union all select 'g' as stu_name, 'C2' as class ,6564 as score union all select 'h' as stu_name, 'C2' as class ,444 as score union all select 'm' as stu_name, 'C2' as class ,111 as score union all select 'n' as stu_name, 'C2' as class ,222 as score union all select 'i' as stu_name, 'C2' as class ,333 as score ) as aa order by class , score desc ) group by class ) as a all left join ( select 'C1' as class , 4 as population union all select 'C2' as class , 3 as population ) as b on a.class = b.class) as aa

SQL peut être converti en:

select class ,arry[1] as stu_name ,arry[2] as score ,arry[3] as student_ranking from ( select aa.class ,arrayConcat(joined_array ,[toString(element_index)] ) as arry from ( select class , arrayJoin(arrayResize(a.array_group , b.population)) as joined_array , indexOf(arrayResize(a.array_group , b.population) ,arrayJoin(arrayResize(a.array_group , b.population))) as element_index from ( select class , groupArray(4)([stu_name , cast(score as String)]) as array_group from ( select * from ( select 'a' as stu_name, 'C1' as class ,213 as score union all select 'b' as stu_name, 'C1' as class ,223 as score union all select 'c' as stu_name, 'C1' as class ,212 as score union all select 'd' as stu_name, 'C1' as class ,877 as score union all select 'e' as stu_name, 'C1' as class ,665 as score union all select 'g' as stu_name, 'C2' as class ,6564 as score union all select 'h' as stu_name, 'C2' as class ,444 as score union all select 'm' as stu_name, 'C2' as class ,111 as score union all select 'n' as stu_name, 'C2' as class ,222 as score union all select 'i' as stu_name, 'C2' as class ,333 as score ) as aa order by class , score desc ) group by class ) as a all left join ( select 'C1' as class , 4 as population union all select 'C2' as class , 3 as population ) as b on a.class = b.class ) as aa ) as aaa

Comment créer une séquence périodique

, intervalle = 7, chaque sept nombres sont divisés en groupe.