hive sql-aggregat

Question

Ich habe zwei Tabellen in Hive, t1 y t2

>describe t1;
>date_id    string

>describe t2;
>messageid string,
 createddate string,
 userid int

> select * from t1 limit 3;        
> 2011-01-01 00:00:00 
  2011-01-02 00:00:00 
  2011-01-03 00:00:00 

> select * from t2 limit 3;
87211389    2011-01-03 23:57:01 13864753
87211656    2011-01-03 23:57:59 13864769
87211746    2011-01-03 23:58:25 13864785

Was ich möchte, ist die Zählung der drei Tage zurückliegenden eindeutigen userid für ein bestimmtes Datum.
Zum Beispiel, für das Datum 2011-01-03 Ich möchte verschiedene userid von 2011-01-01 zu 2011-01-03 .
für Datum 2011-01-04 Ich möchte verschiedene userid von 2011-01-02 zu 2011-01-04

Ich habe die folgende Abfrage geschrieben. Aber sie gibt kein Drei-Tage-Ergebnis zurück. Stattdessen gibt sie unterschiedliche userid pro Tag zurück.

SELECT to_date(t1.date_id), count(distinct t2.userid) FROM t1 JOIN t2 
ON (to_date(t2.createddate) = to_date(t1.date_id))  
WHERE date_sub(to_date(t2.createddate),0) > date_sub(to_date(t1.date_id), 3) 
AND to_date(t2.createddate) <= to_date(t1.date_id) 
GROUP by to_date(t1.date_id);

`to_date()` and `date_sub()` are date function in Hive. 

Das heißt, der folgende Teil wird nicht wirksam.

WHERE date_sub(to_date(t2.createddate),0) > date_sub(to_date(t1.date_id), 3) 
AND to_date(t2.createddate) <= to_date(t1.date_id) 

EDIT: Eine Lösung kann sein (aber es ist super langsam):

SELECT to_date(t3.date_id), count(distinct t3.userid) FROM
(
 SELECT * FROM t1  LEFT OUTER JOIN t2
 WHERE 
 (date_sub(to_date(t2.createddate),0) > date_sub(to_date(t1.date_id), 3)
  AND to_date(t2.createddate) <= to_date(t1.date_id)
 )
) t3 
GROUP by to_date(t3.date_id);

UPDATE: Danke für alle Antworten. Sie sind gut.
Aber Hive ist etwas anders als SQL. Leider können sie in HIVE nicht verwendet werden. Meine derzeitige Lösung ist die Verwendung von UNION ALL .

 SELECT * FROM t1 JOIN t2 ON (to_date(t1.date_id) = to_date(t2.createddate))
 UNION ALL
 SELECT * FROM t1 JOIN t2 ON (to_date(t1.date_id) = date_add(to_date(t2.createddate), 1)
 UNION ALL 
 SELECT * FROM t1 JOIN t2 ON (to_date(t1.date_id) = date_add(to_date(t2.createddate), 2)

Dann mache ich group by y count . Auf diese Weise kann ich bekommen, was ich will.
Es ist zwar nicht elegant, aber viel effizienter als cross join .

Answer 1

Sie benötigen eine Unterabfrage:

Versuchen Sie etwas wie dies (ich kann nicht testen, weil ich keinen Hive habe)

SELECT to_date(t1.date_id), count(distinct t2.userid) FROM t1 JOIN t2 
ON (to_date(t2.createddate) = to_date(t1.date_id))  
WHERE t2.messageid in 
    (
    select t2.messageid from t2 where 
    date_sub(to_date(t2.createddate),0) > date_sub(to_date(t1.date_id), 3) 
    AND 
    to_date(t2.createddate) <= to_date(t1.date_id) 
   )
GROUP by to_date(t1.date_id);

der Schlüssel ist, dass mit der Unterabfrage FOR EACH date in t1 die richtigen Datensätze in t2 ausgewählt werden.

EDITです：

Forcing subquery in von Klausel könnten Sie dies versuchen:

SELECT to_date(t1.date_id), count(distinct t2.userid) FROM t1 JOIN 

(select userid, createddate  from t2 where 

    date_sub(to_date(t2.createddate),0) > date_sub(to_date(t1.date_id), 3) 
    AND 
    to_date(t2.createddate) <= to_date(t1.date_id) 
) as t2

ON (to_date(t2.createddate) = to_date(t1.date_id))  

GROUP by to_date(t1.date_id);

aber ich weiß nicht, ob es funktionieren könnte.

Answer 2

Ich gehe davon aus, dass t1 verwendet wird, um den Zeitraum von 3 Tagen zu definieren. Ich vermute, der rätselhafte Ansatz ist auf die Unzulänglichkeiten von Hive zurückzuführen. So können Sie eine beliebige Anzahl von 3-Tage-Perioden haben. Versuchen Sie die folgenden 2 Abfragen

SELECT substring(t1.date_id,1,10), count(distinct t2.userid) 
FROM t1 
JOIN t2 
ON substring(t2.createddate,1,10) >= date_sub(substring(t1.date_id,1,10), 2) 
AND substring(t2.createddate,1,10) <=  substring(t1.date_id,1,10) 
GROUP BY t1.date_id 

--oder--

SELECT substring(t1.date_id,1,10), count(distinct t2.userid) 
FROM t1 
JOIN t2 
ON t2.createddate like substring(t1.date_id ,1,10) + '%' 
OR t2.createddate like substring(date_sub(t1.date_id, 1) ,1,10) + '%' 
OR t2.createddate like substring(date_sub(t1.date_id, 2) ,1,10) + '%' 
GROUP BY t1.date_id 

Letzteres minimiert die Funktionsaufrufe in der Tabelle t2. Ich gehe auch davon aus, dass t1 der kleinere der beiden ist. substring sollte das gleiche Ergebnis liefern wie to_date. Laut der Dokumentation, https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#SprachhandbuchUDF-DateFunktionen to_date gibt einen String-Datentyp zurück. Die Unterstützung für Datumsdatentypen scheint minimal zu sein, aber ich bin mit Hive nicht vertraut.