Cluster Arrays

Working with Cluster Arrays

A cluster array in Sheerpower is a powerful data structure that functions like an in-memory spreadsheet, containing rows and columns of data. They are ideal for handling collections of related information efficiently. Like vectors in other languages, cluster arrays do not require preallocation—they grow automatically as needed. Sheerpower provides a rich set of built-in features for working with this data, allowing operations such as sorting, filtering, and searching, much like you would in a database.

Graphic 1: A Cluster Array as a Spreadsheet

This `inventory` cluster holds multiple records (rows), each with the same set of fields (columns).

item_id$	product_name$	quantity	price
WID-001	Standard Widget	150	19.99
GAD-002	Super Gadget	75	29.95
DOO-003	Doodad Pro	200	9.50

With enough RAM and pagefile space, cluster arrays can support up to one billion rows with key lookup speeds of millions per second.

1. Defining and Populating a Cluster Array

First, you define the structure of your cluster. Then, you can add records (rows) to it using the add cluster statement.

! Define the structure for our inventory cluster inventory: item_id$, product_name$, quantity, price ! Add the first record to the cluster array add cluster inventory inventory->item_id$ = "WID-001" inventory->product_name$ = "Standard Widget" inventory->quantity = 150 inventory->price = 19.99 end add ! Add a second record add cluster inventory inventory->item_id$ = "GAD-002" inventory->product_name$ = "Super Gadget" inventory->quantity = 75 inventory->price = 29.95 end add

2. Understanding the "Current Row"

Sheerpower keeps track of a current row within the cluster array. When you add a new record, that new record automatically becomes the current row. To work with a different record, you must first make it current using the set cluster statement.

Graphic 2: Moving the Current Row Pointer

	item_id$	product_name$
	WID-001	Standard Widget
⇒	GAD-002	Super Gadget
	DOO-003	Doodad Pro

After set cluster inventory: row 2, the second row is now the current one.

! After adding the Super Gadget, the second row is current print "Current product: "; inventory->product_name$ ! Let's make the first row current and print its name set cluster inventory: row 1 print "First product: "; inventory->product_name$

3. Updating and Deleting Records

Modifying a record is simple: first make the record current, then assign a new value to its field.

! Let's update the quantity of the first item set cluster inventory: row 1 inventory->quantity = 145 ! Update the quantity print "Updated quantity: "; inventory->quantity

Deleting records is also straightforward. For a detailed guide on removing rows, see Reset Cluster.

4. Iterating Over Records: The `collect` and `for each` Pattern

To process every record in a cluster array, you use a two-step pattern. This design is powerful because it separates the act of gathering/filtering data from the act of processing it.

collect ... end collect: This block builds a temporary collection (or view) of your data. This is where you do all your filtering (include/exclude) and sorting.
for each ... next: This block then loops through the temporary collection you just created.

! First, create a collection of items with low stock (< 100), sorted by name collect cluster inventory include inventory->quantity < 100 sort by inventory->product_name$ end collect ! Now, loop through that filtered collection and print each item for each inventory print inventory->product_name$; " (Quantity: "; inventory->quantity; ")" next inventory

Limiting results after sorting:

There is no limit keyword inside collect cluster. This is by design — a limit applied before sorting would select arbitrary rows, not the top or bottom N values.

The correct pattern is to sort in the collect block, then use a counter with exit for in the for each loop:

collect cluster sales sort descending by sales->amount end collect row_count = 0 for each sales row_count++ if row_count > 10 then exit for print sales->name$; ' '; sales->amount next sales

This guarantees the sort completes across the full dataset before the top N rows are selected.

unique - One Row per Distinct Value

Use unique with collect cluster to create a collected view containing one row for each distinct value of a field.

collect cluster clustername: unique clustername->fieldname ... ... ... end collect

For each distinct field value, the first matching row in cluster file order becomes the representative row for that group.

The underlying cluster is not changed. The size() function still returns the total number of rows in the cluster, including duplicates. To obtain the number of distinct values, use _extracted after the end collect statement.

How unique Finds Distinct Values

Distinct values and their occurrence counts come directly from the field's hash index. As rows are added to the cluster, the index maintains a count for each indexed value.

Because this information already exists, unique does not need to scan the cluster or count duplicate rows at collect time. Its work does not increase merely because a value occurs many times.

Inside the collect cluster block, _extracted contains the occurrence count for the current group. It tells how many cluster rows have the current distinct field value.

After end collect, _extracted contains the number of distinct values retained in the collected view.

Creating a Word-Frequency View

The following example creates one collected row for each distinct word and stores the word's frequency in the representative row:

collect cluster words: unique words->word$ words->count = _extracted sort descending by words->count end collect distinct_words = _extracted

Inside the block, _extracted is the number of times the current word occurs. After the block, it is the number of distinct words in the collected view.

That single block performs the equivalent of a GROUP BY operation together with a COUNT for every group.

Filtering Groups

Unique grouping takes place before include and exclude expressions are evaluated.

Each expression is evaluated once for each distinct value, using its representative row. The group's complete occurrence count is available as _extracted. This makes group-level filtering a one-line operation.

For example, the following collection retains one representative row for every city name that occurs more than once:

collect cluster population: unique population->city$ include _extracted > 1 end collect print 'city names that are repeated: '; _extracted

After end collect, the collected view contains one row for each repeated city name. At that position, _extracted contains the number of repeated city names retained in the view.

5. Built-in Functions for Instant Data Aggregation and Statistics

Sheerpower provides built-in functions that can instantly calculate aggregate values across an entire cluster array field, eliminating the need for manual loops.

print "Total items in stock: "; stats$sum(inventory->quantity) print "Most expensive item: $"; stats$max(inventory->price) print "Cheapest item: $"; stats$min(inventory->price) print "Average price: $"; stats$mean(inventory->price) print "Number of different products: "; size(inventory)

In addition, over 30 statistical functions are provided. See Cluster Statistics.

6. High-Speed Searching with `findrow()`

The findrow() function is highly optimized to perform millions of lookups per second. It returns the row number of the first record that matches your search criteria and make the row current, or 0 if no match is found. The findrow() function works with any Sheerpower data type.

! Let's find the "Super Gadget" item_to_find$ = "Super Gadget" row_num = findrow(inventory->product_name$, item_to_find$) if row_num > 0 then print "Found "; item_to_find$; " at row "; row_num print "Its price is $"; inventory->price else print item_to_find$; " not found." end if

Note: When you use findrow(), the field you search on is automatically optimized as a key field for future lookups, making subsequent searches even faster. This happens dynamically without any need for manual index configuration. A successful findrow() automatically makes the row current.

For a detailed guide on findrow(), see High-speed Lookups with Clusters: FINDROW(), FINDVALUE(), and FINDVALUE$().

Summary: Cluster arrays provide efficient data handling in Sheerpower. They combine the simplicity of a spreadsheet with the power of high-speed, database-like operations. By mastering their use, you can build applications that are both powerful and easy to maintain.

(Show/Hide Sheerpower Cluster Arrays Takeaways)

Hide Description

a$ b$ x y

Enter or modify the code below, and then click on RUN

Looking for the full power of Sheerpower?
Check out the Sheerpower website. Free to download. Free to use.