From 8981adda6a24a172245b8cd0dd60e31644e23c75 Mon Sep 17 00:00:00 2001 From: Vikas Date: Tue, 3 Jul 2018 17:05:36 +0530 Subject: Added aggregate functions for external data - Sum, which returns the sum of values in the column. - Average, which returns the average of values in the column. - Min, which returns the minimum of the values in the column. - Max, which returns the maximum of the values in the column. Change-Id: I196eb2d367d2f8c50ceec42735f6f56e2067e401 Reviewed-on: https://gerrit.libreoffice.org/56862 Tested-by: Jenkins Reviewed-by: Markus Mohrhard --- sc/qa/unit/datatransformation_test.cxx | 80 +++++++++++++++++++ sc/source/ui/dataprovider/datatransformation.cxx | 98 ++++++++++++++++++++++++ sc/source/ui/inc/datatransformation.hxx | 16 +++- 3 files changed, 193 insertions(+), 1 deletion(-) diff --git a/sc/qa/unit/datatransformation_test.cxx b/sc/qa/unit/datatransformation_test.cxx index 8cec163eeb43..c011941a6cb7 100644 --- a/sc/qa/unit/datatransformation_test.cxx +++ b/sc/qa/unit/datatransformation_test.cxx @@ -35,6 +35,10 @@ public: void testTextToUpper(); void testTextCapitalize(); void testTextTrim(); + void testAggregateSum(); + void testAggregateAverage(); + void testAggregateMin(); + void testAggregateMax(); CPPUNIT_TEST_SUITE(ScDataTransformationTest); CPPUNIT_TEST(testColumnRemove); @@ -44,6 +48,10 @@ public: CPPUNIT_TEST(testTextToUpper); CPPUNIT_TEST(testTextCapitalize); CPPUNIT_TEST(testTextTrim); + CPPUNIT_TEST(testAggregateSum); + CPPUNIT_TEST(testAggregateAverage); + CPPUNIT_TEST(testAggregateMin); + CPPUNIT_TEST(testAggregateMax); CPPUNIT_TEST_SUITE_END(); private: @@ -193,6 +201,78 @@ void ScDataTransformationTest::testTextTrim() CPPUNIT_ASSERT_EQUAL(OUString("Paris"), m_pDoc->GetString(2, 2, 0)); } +void ScDataTransformationTest::testAggregateSum() +{ + m_pDoc->SetValue(2, 0, 0, 2034); + m_pDoc->SetValue(2, 1, 0, 2342); + m_pDoc->SetValue(2, 2, 0, 57452); + + m_pDoc->SetValue(4, 0, 0, 4829.98); + m_pDoc->SetValue(4, 1, 0, 53781.3); + m_pDoc->SetValue(4, 2, 0, 9876.4); + m_pDoc->SetValue(4, 3, 0, 0); + + sc::AggregateFunction aTransform({2, 4}, sc::AGGREGATE_FUNCTION::SUM); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(61828, m_pDoc->GetValue(2, 4, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(68487.68, m_pDoc->GetValue(4, 4, 0), 1e-10); +} + +void ScDataTransformationTest::testAggregateAverage() +{ + m_pDoc->SetValue(2, 0, 0, 2034); + m_pDoc->SetValue(2, 1, 0, 2342); + m_pDoc->SetValue(2, 2, 0, 57453); + + m_pDoc->SetValue(3, 0, 0, 4); + m_pDoc->SetValue(3, 1, 0, 4); + m_pDoc->SetValue(3, 2, 0, 4); + + sc::AggregateFunction aTransform({2, 3}, sc::AGGREGATE_FUNCTION::AVERAGE); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(20609.6666666667, m_pDoc->GetValue(2, 3, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(4, m_pDoc->GetValue(3, 3, 0), 1e-10); +} + +void ScDataTransformationTest::testAggregateMin() +{ + m_pDoc->SetValue(2, 0, 0, 2034); + m_pDoc->SetValue(2, 1, 0, 2342); + m_pDoc->SetValue(2, 2, 0, 57453); + + m_pDoc->SetValue(3, 0, 0, 2034); + m_pDoc->SetValue(3, 1, 0, -2342); + m_pDoc->SetValue(3, 2, 0, 57453); + + sc::AggregateFunction aTransform({2, 3}, sc::AGGREGATE_FUNCTION::MIN); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(2034, m_pDoc->GetValue(2, 3, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(-2342, m_pDoc->GetValue(3, 3, 0), 1e-10); +} + +void ScDataTransformationTest::testAggregateMax() +{ + m_pDoc->SetValue(2, 0, 0, 2034); + m_pDoc->SetValue(2, 1, 0, 2342); + m_pDoc->SetValue(2, 2, 0, 57453); + m_pDoc->SetValue(2, 3, 0, -453); + + m_pDoc->SetValue(3, 0, 0, 2034); + m_pDoc->SetValue(3, 1, 0, -2342); + m_pDoc->SetValue(3, 2, 0, -57453); + m_pDoc->SetValue(3, 3, 0, -453); + + sc::AggregateFunction aTransform({2, 3}, sc::AGGREGATE_FUNCTION::MAX); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(57453, m_pDoc->GetValue(2, 4, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(2034, m_pDoc->GetValue(3, 4, 0), 1e-10); +} + + ScDataTransformationTest::ScDataTransformationTest() : ScBootstrapFixture( "sc/qa/unit/data/dataprovider" ), m_pDoc(nullptr) diff --git a/sc/source/ui/dataprovider/datatransformation.cxx b/sc/source/ui/dataprovider/datatransformation.cxx index 84df313aacdc..fd779bf6587a 100644 --- a/sc/source/ui/dataprovider/datatransformation.cxx +++ b/sc/source/ui/dataprovider/datatransformation.cxx @@ -10,6 +10,7 @@ #include #include +#include namespace sc { @@ -268,6 +269,103 @@ TransformationType TextTransformation::getTransformationType() const return TransformationType::TEXT_TRANSFORMATION; } +AggregateFunction::AggregateFunction(const std::set& rColumns, const AGGREGATE_FUNCTION rType): + maColumns(rColumns), + maType(rType) +{ +} + +void AggregateFunction::Transform(ScDocument& rDoc) const +{ + SCROW nEndRow = 0; + for (auto& itr : maColumns) + { + nEndRow = getLastRow(rDoc, itr); + } + + for (auto& rCol : maColumns) + { + switch (maType) + { + case AGGREGATE_FUNCTION::SUM: + { + double nSum = 0; + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(rCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(rCol, nRow, 0); + nSum += nVal; + } + } + rDoc.SetValue(rCol, nEndRow + 1, 0, nSum); + } + break; + case AGGREGATE_FUNCTION::AVERAGE: + { + double nSum = 0; + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(rCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(rCol, nRow, 0); + nSum += nVal; + } + } + + double nAvg = nSum / (nEndRow + 1); + rDoc.SetValue(rCol, nEndRow + 1, 0, nAvg); + } + break; + case AGGREGATE_FUNCTION::MIN: + { + double nMin = std::numeric_limits::max(); + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(rCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(rCol, nRow, 0); + if(nVal < nMin) + nMin = nVal; + } + } + rDoc.SetValue(rCol, nEndRow + 1, 0, nMin); + } + break; + case AGGREGATE_FUNCTION::MAX: + { + double nMax = std::numeric_limits::lowest(); + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(rCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(rCol, nRow, 0); + if(nMax < nVal) + nMax = nVal; + } + } + rDoc.SetValue(rCol, nEndRow + 1, 0, nMax); + } + break; + default: + break; + } + } +} + +TransformationType AggregateFunction::getTransformationType() const +{ + return TransformationType::AGGREGATE_FUNCTION; +} + } /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/inc/datatransformation.hxx b/sc/source/ui/inc/datatransformation.hxx index 522c039851ae..305e35a3cd54 100644 --- a/sc/source/ui/inc/datatransformation.hxx +++ b/sc/source/ui/inc/datatransformation.hxx @@ -27,11 +27,14 @@ enum class TransformationType SPLIT_TRANSFORMATION, DELETE_TRANSFORMATION, SORT_TRANSFORMATION, - TEXT_TRANSFORMATION + TEXT_TRANSFORMATION, + AGGREGATE_FUNCTION }; enum class TEXT_TRANSFORM_TYPE { TO_LOWER, TO_UPPER, CAPITALIZE, TRIM }; +enum class AGGREGATE_FUNCTION { SUM, AVERAGE, MIN, MAX }; + class SC_DLLPUBLIC DataTransformation { protected: @@ -110,6 +113,17 @@ class SC_DLLPUBLIC TextTransformation : public DataTransformation virtual TransformationType getTransformationType() const override; }; +class SC_DLLPUBLIC AggregateFunction : public DataTransformation +{ + std::set maColumns; + AGGREGATE_FUNCTION maType; + + public: + AggregateFunction(const std::set& rColumns, const AGGREGATE_FUNCTION rType); + virtual void Transform(ScDocument& rDoc) const override; + virtual TransformationType getTransformationType() const override; +}; + } #endif -- cgit