[GitHub] [arrow-datafusion] WinkerDu opened a new issue, #2404: `sum(distinct)` support

[GitBox <gi...@apache.org>]

WinkerDu opened a new issue, #2404:
URL: https://github.com/apache/arrow-datafusion/issues/2404

   **Is your feature request related to a problem or challenge? Please describe what you are trying to do.**
   
   For now, DataFusion hasn't support `sum(distinct)` yet. Though optimizer `SingleDistinctToGroupBy` (#1315) supports single distinct usage, there are more SQL scenes have not been covered.
   For example,  run the following unit test,
   ```
   #[tokio::test]
   async fn query_sum_distinct() -> Result<()> {
       let schema = Arc::new(Schema::new(vec![
           Field::new("c1", DataType::Int64, true),
           Field::new("c2", DataType::Int64, true),
       ]));
   
       let data = RecordBatch::try_new(
           schema.clone(),
           vec![
               Arc::new(Int64Array::from(vec![
                   Some(0),
                   Some(1),
                   None,
                   Some(3),
                   Some(3),
               ])),
               Arc::new(Int64Array::from(vec![
                   None,
                   Some(1),
                   Some(1),
                   Some(2),
                   Some(2),
               ])),
           ],
       )?;
   
       let table = MemTable::try_new(schema, vec![vec![data]])?;
       let ctx = SessionContext::new();
       ctx.register_table("test", Arc::new(table))?;
   
       // 2 different aggregate functions: avg and sum(distinct)
       let sql = "SELECT AVG(c1), SUM(DISTINCT c2) FROM test";
       let actual = execute_to_batches(&ctx, sql).await;
       Ok(())
   }
   ```
   then error raises like
   ```
   NotImplemented(\"SUM(DISTINCT) aggregations are not available\") at Creating physical plan for 'SELECT AVG(c1), SUM(DISTINCT c2) FROM test': Projection: #AVG(test.c1), #SUM(DISTINCT test.c2)\n  Aggregate: groupBy=[[]], aggr=[[AVG(#test.c1), SUM(DISTINCT #test.c2)]]\n    TableScan: test projection=Some([0, 1])
   ```
   
   **Describe the solution you'd like**
   Introduces `expressions::DistinctSum` into DF
   - Maintains a `HashSet` to record unique numeric list, update `HashSet` when new item input.
   - Aggregate state stores in `ScalarValue::List`, which Built from `HashSet`.
   - During Final aggregate, evaluate `DistinctSum` by computing sum from numeric stored in `HashSet`
   
   **Describe alternatives you've considered**
   No.
   
   **Additional context**
   No.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [arrow-datafusion] alamb closed issue #2404: `sum(distinct)` support

[GitBox <gi...@apache.org>]

alamb closed issue #2404: `sum(distinct)` support
URL: https://github.com/apache/arrow-datafusion/issues/2404


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org