Create aggregated table with elasticsearch like MySQL

Hello,

I'm new and I like more and more elasticsearch.
I'm working on a statistic dashboard actually on MySQL whose I'm trying to
transform on elasticsearch.

That's my index :

{
'phone_number': '0123456789',
'status': 'Busy',
'site_name': 'toto',
'call_duration': 82,
'url': 'http://localhost/test',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 1, 14, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0223456789',
'status': 'HangUp',
'site_name': 'pipo',
'call_duration': 100,
'url': 'http://localhost/index',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 14, 12, 42),
'price': 1.5,
'browser': 'Google Chrome'
} {
'phone_number': '0333456789',
'status': 'HangUp',
'site_name': 'pouet',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 16, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0443456789',
'status': 'Busy',
'site_name': 'tutu',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 12, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0553456789',
'status': 'Invalid',
'site_name': 'tutu',
'call_duration': 50,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 17, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
}

I would like create a table like that :
DATE | CALL_DURATION | PRICE | Status HangUp | Status busy | Status
Invalid

2013-06-03 | 132 | 2.2 | 0 | 1 | 1
2013-06-02 | 182 | 2.6 | 2 | 0 |0

I build this query :
{
'query': {
'filtered': {
'filter': {
'range': {
'date': {
'to': datetime.datetime(2013, 6, 3, 0, 0),
'include_upper': False,
'from': datetime.datetime(2013, 6, 1, 0, 0)
}
}
},
'query': {
'match_all': {}
}
}
},
'facets': {
'date_facet_price': {
'date_histogram': {
'value_field': 'price',
'interval': 'day',
'key_field': 'date'
}
},
'date_facet_call': {
'date_histogram': {
'value_field': 'call_duration',
'interval': 'day',
'key_field': 'date'
}
}
}
}

I've got this result :

{

  • date_facet_price:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 8680.499999999962,
      - total_count: 8149,
      - min: 0,
      - max: 6.03,
      - time: 1370044800000,
      - mean: 1.0652227267149297
      },

      {
      - count: 2325,
      - total: 2374.9300000000003,
      - total_count: 2325,
      - min: 0,
      - max: 6.33,
      - time: 1370131200000,
      - mean: 1.0214752688172044
      },

      {
      - count: 5199,
      - total: 5658.63999999999,
      - total_count: 5199,
      - min: 0,
      - max: 5,
      - time: 1370217600000,
      - mean: 1.0884093094825908
      }
      ]
      },
  • date_facet_call:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 1,
      - total_count: 8149,
      - min: 0,
      - max: 4,
      - time: 1370044800000,
      - mean: 0.011657872131549884
      },

      {
      - count: 2325,
      - total: 2,
      - total_count: 2325,
      - min: 0,
      - max: 1,
      - time: 1370131200000,
      - mean: 0.01032258064516129
      },

      {
      - count: 5199,
      - total: 3,
      - total_count: 5199,
      - min: 50,
      - max: 100,
      - time: 1370217600000,
      - mean: 0.0044239276783996926
      }
      ]
      },

I'would like to recover the status distribution by day, like mean of price
for example :.

  • status:
    {
    • _type: "terms",
    • total: 15673,
    • terms:
      [

      {
      - count: 2,
      - term: "HangUp",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Busy",
      time: 1370131200000
      },

      {
      - count: 3,
      - term: "NoAnswer",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Invalid"
      }
      ],
    • other: 0,
    • missing: 0
      },

I hope I was clear.

Thank you for read.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Currently hierarchal facets are not supported. Hopefully they will be in
the future. The only solution I can think of is to execute another faceted
query with facets on status for each individual day.

--
Ivan

On Tue, Jun 11, 2013 at 3:17 AM, Rémy Turpin remy.turpin@gmail.com wrote:

Hello,

I'm new and I like more and more elasticsearch.
I'm working on a statistic dashboard actually on MySQL whose I'm trying to
transform on elasticsearch.

That's my index :

{
'phone_number': '0123456789',
'status': 'Busy',
'site_name': 'toto',
'call_duration': 82,
'url': 'http://localhost/test',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 1, 14, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0223456789',
'status': 'HangUp',
'site_name': 'pipo',
'call_duration': 100,
'url': 'http://localhost/index',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 14, 12, 42),
'price': 1.5,
'browser': 'Google Chrome'
} {
'phone_number': '0333456789',
'status': 'HangUp',
'site_name': 'pouet',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 16, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0443456789',
'status': 'Busy',
'site_name': 'tutu',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 12, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0553456789',
'status': 'Invalid',
'site_name': 'tutu',
'call_duration': 50,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 17, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
}

I would like create a table like that :
DATE | CALL_DURATION | PRICE | Status HangUp | Status busy | Status
Invalid

2013-06-03 | 132 | 2.2 | 0 | 1 | 1
2013-06-02 | 182 | 2.6 | 2 | 0 |0

I build this query :
{
'query': {
'filtered': {
'filter': {
'range': {
'date': {
'to': datetime.datetime(2013, 6, 3, 0, 0),
'include_upper': False,
'from': datetime.datetime(2013, 6, 1, 0, 0)
}
}
},
'query': {
'match_all': {}
}
}
},
'facets': {
'date_facet_price': {
'date_histogram': {
'value_field': 'price',
'interval': 'day',
'key_field': 'date'
}
},
'date_facet_call': {
'date_histogram': {
'value_field': 'call_duration',
'interval': 'day',
'key_field': 'date'
}
}
}
}

I've got this result :

{

  • date_facet_price:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 8680.499999999962,
      - total_count: 8149,
      - min: 0,
      - max: 6.03,
      - time: 1370044800000,
      - mean: 1.0652227267149297
      },

      {
      - count: 2325,
      - total: 2374.9300000000003,
      - total_count: 2325,
      - min: 0,
      - max: 6.33,
      - time: 1370131200000,
      - mean: 1.0214752688172044
      },

      {
      - count: 5199,
      - total: 5658.63999999999,
      - total_count: 5199,
      - min: 0,
      - max: 5,
      - time: 1370217600000,
      - mean: 1.0884093094825908
      }
      ]
      },
  • date_facet_call:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 1,
      - total_count: 8149,
      - min: 0,
      - max: 4,
      - time: 1370044800000,
      - mean: 0.011657872131549884
      },

      {
      - count: 2325,
      - total: 2,
      - total_count: 2325,
      - min: 0,
      - max: 1,
      - time: 1370131200000,
      - mean: 0.01032258064516129
      },

      {
      - count: 5199,
      - total: 3,
      - total_count: 5199,
      - min: 50,
      - max: 100,
      - time: 1370217600000,
      - mean: 0.0044239276783996926
      }
      ]
      },

I'would like to recover the status distribution by day, like mean of price
for example :.

  • status:
    {
    • _type: "terms",
    • total: 15673,
    • terms:
      [

      {
      - count: 2,
      - term: "HangUp",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Busy",
      time: 1370131200000
      },

      {
      - count: 3,
      - term: "NoAnswer",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Invalid"
      }
      ],
    • other: 0,
    • missing: 0
      },

I hope I was clear.

Thank you for read.

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

OK thank's, we know when this fonctionnality will be implemented ?

Le mercredi 12 juin 2013 16:56:07 UTC+2, Ivan Brusic a écrit :

Currently hierarchal facets are not supported. Hopefully they will be in
the future. The only solution I can think of is to execute another faceted
query with facets on status for each individual day.

--
Ivan

On Tue, Jun 11, 2013 at 3:17 AM, Rémy Turpin <remy....@gmail.com<javascript:>

wrote:

Hello,

I'm new and I like more and more elasticsearch.
I'm working on a statistic dashboard actually on MySQL whose I'm trying
to transform on elasticsearch.

That's my index :

{
'phone_number': '0123456789',
'status': 'Busy',
'site_name': 'toto',
'call_duration': 82,
'url': 'http://localhost/test',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 1, 14, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0223456789',
'status': 'HangUp',
'site_name': 'pipo',
'call_duration': 100,
'url': 'http://localhost/index',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 14, 12, 42),
'price': 1.5,
'browser': 'Google Chrome'
} {
'phone_number': '0333456789',
'status': 'HangUp',
'site_name': 'pouet',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 16, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0443456789',
'status': 'Busy',
'site_name': 'tutu',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 12, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0553456789',
'status': 'Invalid',
'site_name': 'tutu',
'call_duration': 50,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 17, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
}

I would like create a table like that :
DATE | CALL_DURATION | PRICE | Status HangUp | Status busy | Status
Invalid

2013-06-03 | 132 | 2.2 | 0 | 1 | 1
2013-06-02 | 182 | 2.6 | 2 | 0 |0

I build this query :
{
'query': {
'filtered': {
'filter': {
'range': {
'date': {
'to': datetime.datetime(2013, 6, 3, 0, 0),
'include_upper': False,
'from': datetime.datetime(2013, 6, 1, 0, 0)
}
}
},
'query': {
'match_all': {}
}
}
},
'facets': {
'date_facet_price': {
'date_histogram': {
'value_field': 'price',
'interval': 'day',
'key_field': 'date'
}
},
'date_facet_call': {
'date_histogram': {
'value_field': 'call_duration',
'interval': 'day',
'key_field': 'date'
}
}
}
}

I've got this result :

{

  • date_facet_price:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 8680.499999999962,
      - total_count: 8149,
      - min: 0,
      - max: 6.03,
      - time: 1370044800000,
      - mean: 1.0652227267149297
      },

      {
      - count: 2325,
      - total: 2374.9300000000003,
      - total_count: 2325,
      - min: 0,
      - max: 6.33,
      - time: 1370131200000,
      - mean: 1.0214752688172044
      },

      {
      - count: 5199,
      - total: 5658.63999999999,
      - total_count: 5199,
      - min: 0,
      - max: 5,
      - time: 1370217600000,
      - mean: 1.0884093094825908
      }
      ]
      },
  • date_facet_call:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 1,
      - total_count: 8149,
      - min: 0,
      - max: 4,
      - time: 1370044800000,
      - mean: 0.011657872131549884
      },

      {
      - count: 2325,
      - total: 2,
      - total_count: 2325,
      - min: 0,
      - max: 1,
      - time: 1370131200000,
      - mean: 0.01032258064516129
      },

      {
      - count: 5199,
      - total: 3,
      - total_count: 5199,
      - min: 50,
      - max: 100,
      - time: 1370217600000,
      - mean: 0.0044239276783996926
      }
      ]
      },

I'would like to recover the status distribution by day, like mean of
price for example :.

  • status:
    {
    • _type: "terms",
    • total: 15673,
    • terms:
      [

      {
      - count: 2,
      - term: "HangUp",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Busy",
      time: 1370131200000
      },

      {
      - count: 3,
      - term: "NoAnswer",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Invalid"
      }
      ],
    • other: 0,
    • missing: 0
      },

I hope I was clear.

Thank you for read.

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearc...@googlegroups.com <javascript:>.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Read more in this issue:

On Thu, Jun 13, 2013 at 9:39 AM, Rémy Turpin remy.turpin@gmail.com wrote:

OK thank's, we know when this fonctionnality will be implemented ?

Le mercredi 12 juin 2013 16:56:07 UTC+2, Ivan Brusic a écrit :

Currently hierarchal facets are not supported. Hopefully they will be in
the future. The only solution I can think of is to execute another faceted
query with facets on status for each individual day.

--
Ivan

On Tue, Jun 11, 2013 at 3:17 AM, Rémy Turpin remy....@gmail.com wrote:

Hello,

I'm new and I like more and more elasticsearch.
I'm working on a statistic dashboard actually on MySQL whose I'm trying
to transform on elasticsearch.

That's my index :

{
'phone_number': '0123456789',
'status': 'Busy',
'site_name': 'toto',
'call_duration': 82,
'url': 'http://localhost/test',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 1, 14, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0223456789',
'status': 'HangUp',
'site_name': 'pipo',
'call_duration': 100,
'url': 'http://localhost/index',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 14, 12, 42),
'price': 1.5,
'browser': 'Google Chrome'
} {
'phone_number': '0333456789',
'status': 'HangUp',
'site_name': 'pouet',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 2, 16, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0443456789',
'status': 'Busy',
'site_name': 'tutu',
'call_duration': 82,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 12, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
} {
'phone_number': '0553456789',
'status': 'Invalid',
'site_name': 'tutu',
'call_duration': 50,
'url': 'http://localhost',
'browser_version': '23.0.1271.97',
'date': datetime.datetime(2013, 6, 3, 17, 12, 42),
'price': 1.1,
'browser': 'Google Chrome'
}

I would like create a table like that :
DATE | CALL_DURATION | PRICE | Status HangUp | Status busy | Status
Invalid

2013-06-03 | 132 | 2.2 | 0 | 1 | 1
2013-06-02 | 182 | 2.6 | 2 | 0 |0

I build this query :
{
'query': {
'filtered': {
'filter': {
'range': {
'date': {
'to': datetime.datetime(2013, 6, 3, 0, 0),
'include_upper': False,
'from': datetime.datetime(2013, 6, 1, 0, 0)
}
}
},
'query': {
'match_all': {}
}
}
},
'facets': {
'date_facet_price': {
'date_histogram': {
'value_field': 'price',
'interval': 'day',
'key_field': 'date'
}
},
'date_facet_call': {
'date_histogram': {
'value_field': 'call_duration',
'interval': 'day',
'key_field': 'date'
}
}
}
}

I've got this result :

{

  • date_facet_price:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 8680.499999999962,
      - total_count: 8149,
      - min: 0,
      - max: 6.03,
      - time: 1370044800000,
      - mean: 1.0652227267149297
      },

      {
      - count: 2325,
      - total: 2374.9300000000003,
      - total_count: 2325,
      - min: 0,
      - max: 6.33,
      - time: 1370131200000,
      - mean: 1.0214752688172044
      },

      {
      - count: 5199,
      - total: 5658.63999999999,
      - total_count: 5199,
      - min: 0,
      - max: 5,
      - time: 1370217600000,
      - mean: 1.0884093094825908
      }
      ]
      },
  • date_facet_call:
    {
    • _type: "date_histogram",
    • entries:
      [

      {
      - count: 8149,
      - total: 1,
      - total_count: 8149,
      - min: 0,
      - max: 4,
      - time: 1370044800000,
      - mean: 0.011657872131549884
      },

      {
      - count: 2325,
      - total: 2,
      - total_count: 2325,
      - min: 0,
      - max: 1,
      - time: 1370131200000,
      - mean: 0.01032258064516129
      },

      {
      - count: 5199,
      - total: 3,
      - total_count: 5199,
      - min: 50,
      - max: 100,
      - time: 1370217600000,
      - mean: 0.0044239276783996926
      }
      ]
      },

I'would like to recover the status distribution by day, like mean of
price for example :.

  • status:
    {
    • _type: "terms",
    • total: 15673,
    • terms:
      [

      {
      - count: 2,
      - term: "HangUp",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Busy",
      time: 1370131200000
      },

      {
      - count: 3,
      - term: "NoAnswer",
      time: 1370131200000
      },

      {
      - count: 1,
      - term: "Invalid"
      }
      ],
    • other: 0,
    • missing: 0
      },

I hope I was clear.

Thank you for read.

--
You received this message because you are subscribed to the Google
Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to elasticsearc...@**googlegroups.com.

For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out
.

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.